MySQL MySQL选择一列DISTINCT，与对应的其他列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5967130/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL select one column DISTINCT, with corresponding other columns
提问by m r
ID FirstName LastName
1 John Doe
2 Bugs Bunny
3 John Johnson
I want to select DISTINCTresults from the FirstNamecolumn, but I need the corresponding IDand LastName.
我想DISTINCT从FirstName列中选择结果，但我需要相应的ID和LastName。
The result set needs to show only one John, but with an IDof 1 and a LastNameof Doe.
结果集只需要显示一个John，但带有一个ID1 和一个LastNameDoe。
采纳答案by diEcho
try this query
试试这个查询
SELECT ID, FirstName, LastName FROM table GROUP BY(FirstName)
回答by Brian Driscoll
The DISTINCTkeyword doesn't really work the way you're expecting it to. When you use SELECT DISTINCT col1, col2, col3you are in fact selecting all unique {col1, col2, col3} tuples.
该DISTINCT关键字并没有像您期望的那样真正起作用。当您使用时，SELECT DISTINCT col1, col2, col3您实际上是在选择所有唯一的 {col1, col2, col3} 元组。
回答by fyrye
To avoid potentially unexpected results when using GROUP BYwithout an aggregate function, as is used in the accepted answer, because MySQL is free to retrieve ANYvalue within the data set being grouped when not using an aggregate function [sic]and issues with ONLY_FULL_GROUP_BY. Please consider using an exclusion join.
为了避免在GROUP BY不使用聚合函数的情况下使用时出现潜在的意外结果，正如在接受的答案中使用的那样，因为当不使用聚合函数时，MySQL 可以自由地检索正在分组的数据集中的任何值[原文如此]和ONLY_FULL_GROUP_BY. 请考虑使用排除连接。
Exclusion Join - Unambiguous Entities
排除连接 - 明确的实体
Assuming the firstname and lastname are uniquely indexed (unambiguous), an alternative to GROUP BYis to sort using a LEFT JOINto filter the result set, otherwise known as an exclusion JOIN.
假设 firstname 和 lastname 被唯一索引（明确），替代方法GROUP BY是使用 aLEFT JOIN来过滤结果集，也称为排除 JOIN。
Ascending order (A-Z)
升序(AZ)
To retrieve the distinct firstname ordered by lastname from A-Z
从 AZ 检索按姓氏排序的不同名字
Query
询问
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;
Result
结果
| id | firstname | lastname |
|----|-----------|----------|
| 2 | Bugs | Bunny |
| 1 | John | Doe |
Descending order (Z-A)
降序(ZA)
To retrieve the distinct firstname ordered by lastname from Z-A
从 ZA 检索按姓氏排序的不同名字
Query
询问
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;
Result
结果
| id | firstname | lastname |
|----|-----------|----------|
| 2 | Bugs | Bunny |
| 3 | John | Johnson |
You can then order the resulting data as desired.
然后，您可以根据需要对结果数据进行排序。
Exclusion Join - Ambiguous Entities
排除连接 - 不明确的实体
If the first and last name combination are not unique (ambiguous)and you have multiple rows of the same values, you can filter the result set by including an OR condition on the JOIN criteria to also filter by id.
如果名字和姓氏的组合不是唯一的（不明确的），并且您有多行具有相同的值，则可以通过在 JOIN 条件上包含 OR 条件来过滤结果集，以便也按 id 进行过滤。
table_name data
表名数据
(1, 'John', 'Doe'),
(2, 'Bugs', 'Bunny'),
(3, 'John', 'Johnson'),
(4, 'John', 'Doe'),
(5, 'John', 'Johnson')
Query
询问
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;
Result
结果
| id | firstname | lastname |
|----|-----------|----------|
| 1 | John | Doe |
| 2 | Bugs | Bunny |
Ordered Subquery
有序子查询
EDIT
编辑
My original answer using an ordered subquery, was written prior to MySQL 5.7.5, which is no longer applicable, due to the changes with ONLY_FULL_GROUP_BY. Please use the exclusion join examples above instead.
我使用有序子查询的原始答案是在MySQL 5.7.5之前编写的，由于ONLY_FULL_GROUP_BY. 请改用上面的排除连接示例。
It is also important to note; when ONLY_FULL_GROUP_BYis disabled (original behavior prior to MySQL 5.7.5), the use of GROUP BYwithout an aggregate function may yield unexpected results, because MySQL is free to choose ANYvalue within the data set being grouped [sic].
同样重要的是要注意；当ONLY_FULL_GROUP_BY被禁用时（MySQL 5.7.5 之前的原始行为），使用GROUP BY没有聚合函数可能会产生意外结果，因为 MySQL 可以自由选择正在分组的数据集中的任何值[原文如此]。
Meaning an IDor lastnamevalue may be retrieved that is not associatedwith the retrieved firstnamerow.
这意味着可以检索与检索到的行无关的ID或lastname值。firstname
WARNING
警告
With MySQL GROUP BYmay not yield the expected results when used with ORDER BY
与 MySQL 一起使用时GROUP BY可能不会产生预期的结果ORDER BY
The best method of implementation, to ensure expected results, is to filter the result set scope using an ordered subquery.
确保预期结果的最佳实现方法是使用有序子查询过滤结果集范围。
table_name data
表名数据
(1, 'John', 'Doe'),
(2, 'Bugs', 'Bunny'),
(3, 'John', 'Johnson')
Query
询问
SELECT * FROM (
SELECT * FROM table_name ORDER BY ID DESC
) AS t1
GROUP BY FirstName
Result
结果
| ID | first | last |
|----|-------|---------|
| 2 | Bugs | Bunny |
| 3 | John | Johnson |
Comparison
比较
To demonstrate the unexpected results when using GROUP BYin combination with ORDER BY
展示GROUP BY结合使用时的意外结果ORDER BY
Query
询问
SELECT * FROM table_name GROUP BY FirstName ORDER BY ID DESC
Result
结果
| ID | first | last |
|----|-------|-------|
| 2 | Bugs | Bunny |
| 1 | John | Doe |
回答by sarath
SELECT ID,LastName
From TABLE_NAME
GROUP BY FirstName
HAVING COUNT(*) >=1
回答by Nanhe Kumar
SELECT firstName, ID, LastName from tableName GROUP BY firstName
回答by onlinebaba
How about
怎么样
`SELECT
my_distinct_column,
max(col1),
max(col2),
max(col3)
...
FROM
my_table
GROUP BY
my_distinct_column`
回答by Thomas Langston
Not sure if you can do this with MySQL, but you can use a CTE in T-SQL
不确定你是否可以用 MySQL 做到这一点，但你可以在 T-SQL 中使用 CTE
; WITH tmpPeople AS (
SELECT
DISTINCT(FirstName),
MIN(Id)
FROM People
)
SELECT
tP.Id,
tP.FirstName,
P.LastName
FROM tmpPeople tP
JOIN People P ON tP.Id = P.Id
Otherwise you might have to use a temporary table.
否则，您可能必须使用临时表。
回答by Kurt Peek
As pointed out by fyrye, the accepted answer pertains to older versions of MySQL in which ONLY_FULL_GROUP_BYhad not yet been introduced. With MySQL 8.0.17 (used in this example), unless you disable ONLY_FULL_GROUP_BYyou would get the following error message:
正如fyrye所指出的，已接受的答案与ONLY_FULL_GROUP_BY尚未引入的旧版 MySQL 相关。使用 MySQL 8.0.17（在本示例中使用），除非您禁用，否则ONLY_FULL_GROUP_BY您将收到以下错误消息：
mysql> SELECT id, firstName, lastName FROM table_name GROUP BY firstName;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydatabase.table_name.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
错误 1055 (42000)：SELECT 列表的表达式 #1 不在 GROUP BY 子句中，并且包含非聚合列“mydatabase.table_name.id”，该列在功能上不依赖于 GROUP BY 子句中的列；这与 sql_mode=only_full_group_by 不兼容
One way to work around this not mentioned by fyrye, but described in https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html, is to apply the ANY_VALUE()function to the columns which are not in the GROUP BYclause (idand lastNamein this example):
fyrye未提及但在https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html 中描述的解决此问题的一种方法是将ANY_VALUE()函数应用于列不是在GROUP BY条款（id和lastName在本例中）：
mysql> SELECT ANY_VALUE(id) as id, firstName, ANY_VALUE(lastName) as lastName FROM table_name GROUP BY firstName;
+----+-----------+----------+
| id | firstName | lastName |
+----+-----------+----------+
| 1 | John | Doe |
| 2 | Bugs | Bunny |
+----+-----------+----------+
2 rows in set (0.01 sec)
As written in the aforementioned docs,
正如上述文档中所写，
In this case, MySQL ignores the nondeterminism of address values within each name group and accepts the query. This may be useful if you simply do not care which value of a nonaggregated column is chosen for each group.
ANY_VALUE()is not an aggregate function, unlike functions such asSUM()orCOUNT(). It simply acts to suppress the test for nondeterminism.
在这种情况下，MySQL 会忽略每个名称组内地址值的不确定性并接受查询。如果您根本不关心为每个组选择非聚合列的哪个值，这可能很有用。
ANY_VALUE()不是聚合函数，与SUM()或等函数不同COUNT()。它只是起到抑制非确定性测试的作用。
回答by Antonio Delacruz
Keep in mind when using the group by and order by that MySQL is the ONLY database that allows for columns to be used in the group by and/or order by piece that are not part of the select statement.
请记住，在使用 group by 和 order by 时，MySQL 是唯一允许在 group by 和/或 order by 中使用不属于 select 语句的列的数据库。
So for example: select column1 from table group by column2 order by column3
例如：从表 group by column2 order by column3 中选择 column1
That will not fly in other databases like Postgres, Oracle, MSSQL, etc. You would have to do the following in those databases
这不会在 Postgres、Oracle、MSSQL 等其他数据库中运行。您必须在这些数据库中执行以下操作
select column1, column2, column3 from table group by column2 order by column3
从表 group by column2 order by column3 中选择 column1, column2, column3
Just some info in case you ever migrate your current code to another database or start working in another database and try to reuse code.
只是一些信息，以防您将当前代码迁移到另一个数据库或开始在另一个数据库中工作并尝试重用代码。
回答by John
You can use group by for display distinct values and also corresponding fields.
您可以使用 group by 来显示不同的值以及相应的字段。
select * from tabel_name group by FirstName
Now you got output like this:
现在你得到了这样的输出：
ID FirstName LastName
2 Bugs Bunny
1 John Doe
If you want to answer like
如果你想回答像
ID FirstName LastName
1 John Doe
2 Bugs Bunny
then use this query,
然后使用这个查询，
select * from table_name group by FirstName order by ID
