php - MySQL 选择对同一个表中的多行进行分组

Question

我正在尝试创建一个查询来从同一个表中选择多行，并将它们像数组一样分组。现在我使用 php 来选择它们，如下所示:

$tks = mysqli_query($con,"SELECT * FROM hof ORDER BY tks DESC LIMIT 5");

$top_ths = mysqli_query($con,"SELECT * FROM hof ORDER BY ths DESC LIMIT 1");

$top_tha = mysqli_query($con,"SELECT * FROM hof ORDER BY tha DESC LIMIT 1");

----

我想将它们合并到一个查询中，这样我就得到一个关联数组。像这样的事情:

(SELECT * FROM hol ORDER BY tks DESC LIMIT 5) AS tks
UNION
(SELECT * FROM hol ORDER BY ths DESC LIMIT 1) AS top_ths

因此 tks 包含所有 5 行，top_ths 包含 1 行。是否可以 ？谢谢。

Answer 1

要了解来自哪个组行，请创建附加字段

(SELECT *, 1 as `group` FROM hol ORDER BY tks DESC LIMIT 5)
UNION
(SELECT *, 2 as `group` FROM hol ORDER BY ths DESC LIMIT 1)