原始答案是在MySQL 5.7.5之前编写的,由于使用的默认更改,该答案不再适用ONLY_FULL_GROUP_BY。还需要注意的重要一点ONLY_FULL_GROUP_BY是,禁用GROUP BYMySQL时,不使用聚合函数会产生意外结果,因为MySQL可以自由选择要分组的数据集中的ANY值[sic]。
假设名字和姓氏是唯一索引的,另一种替代方法GROUP BY是使用进行排序LEFT JOIN以过滤结果集。见示范
检索按姓氏降序排列的唯一姓氏(ZA)
SELECT t1.*
FROM table_name AS t1
LEFT JOIN table_name AS t2
ON t1.firstname = t2.firstname
AND t1.lastname < t2.lastname
WHERE t2.id IS NULL;
#Results
| id | firstname | lastname |
|----|-----------|----------|
| 2 | Bugs | Bunny |
| 3 | John | Johnson |
检索按姓氏升序排列的唯一姓氏(AZ)
SELECT t1.*
FROM table_name AS t1
LEFT JOIN table_name AS t2
ON t1.firstname = t2.firstname
AND t1.lastname > t2.lastname
WHERE t2.id IS NULL;
#Results
| id | firstname | lastname |
|----|-----------|----------|
| 2 | Bugs | Bunny |
| 1 | John | Doe |
然后,您可以根据需要对结果数据进行排序。
如果名字和姓氏组合不是唯一的,并且您有多行具有相同的值,则可以通过在联接上包括OR条件来选择特定的ID,从而过滤结果集。见示范。
table_name数据:
(1, 'John', 'Doe'),
(2, 'Bugs', 'Bunny'),
(3, 'John', 'Johnson'),
(4, 'John', 'Doe'),
(5, 'John', 'Johnson')
SELECT t1.*
FROM table_name AS t1
LEFT JOIN table_name AS t2
ON t1.firstname = t2.firstname
AND (t1.lastname > t2.lastname
OR (t1.firstname = t1.firstname AND t1.lastname = t2.lastname AND t1.id > t2.id))
WHERE t2.id IS NULL;
#Results
| id | firstname | lastname |
|----|-----------|----------|
| 1 | John | Doe |
| 2 | Bugs | Bunny |
警告
与MySQL结合使用GROUP BY时,并不总是会产生预期的结果,ORDER BY请参阅:测试用例示例。
确保预期结果的最佳实现方法是像这样使用Subquery过滤结果集范围。
table_name数据:
(1, 'John', 'Doe'),
(2, 'Bugs', 'Bunny'),
(3, 'John', 'Johnson')
询问
SELECT * FROM (
SELECT * FROM table_name ORDER BY ID DESC
) AS t1
GROUP BY FirstName
#Results
| ID | first | last |
|----|-------|---------|
| 2 | Bugs | Bunny |
| 3 | John | Johnson |
与
SELECT * FROM table_name GROUP BY FirstName ORDER BY ID DESC
#Results
| ID | first | last |
|----|-------|-------|
| 2 | Bugs | Bunny |
| 1 | John | Doe |