MySQL 选择一列 DISTINCT 以及相应的其他列

ID   FirstName   LastName
1      John        Doe
2      Bugs        Bunny
3      John        Johnson

我想从 FirstName列中选择 DISTINCT结果，但是我需要相应的 ID和 LastName。

结果集只需要显示一个 John，但是 ID为1，LastName为 Doe。

369309 次浏览
小开

DISTINCT关键字并不像你期望的那样工作。当你使用SELECT DISTINCT col1, col2, col3时，你实际上选择了所有唯一的{col1, col2, col3}元组。

小开
最佳答案

试试这个查询

 SELECT ID, FirstName, LastName FROM table GROUP BY(FirstName)
小开

不确定是否可以用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

否则，您可能不得不使用临时表。

小开 
SELECT ID,LastName
From TABLE_NAME
GROUP BY FirstName
HAVING COUNT(*) >=1
小开 
SELECT DISTINCT(firstName), ID, LastName from tableName GROUP BY firstName

在我看来这是最好的选择吗

小开 
SELECT firstName, ID, LastName from tableName GROUP BY firstName
小开

您可以使用组by来显示不同的值和相应的字段。

select * from tabel_name group by FirstName

现在你得到了这样的输出:

ID    FirstName     LastName
2     Bugs          Bunny
1     John          Doe
< p > < br > 如果你想回答

ID    FirstName     LastName
1     John          Doe
2     Bugs          Bunny

然后使用这个查询，

select * from table_name group by FirstName order by ID
小开

为了避免使用GROUP BY而不使用聚合函数As用于接受的回答时可能出现的意外结果，因为MySQL在不使用聚合函数(原文如此)和存在ONLY_FULL_GROUP_BY问题时可以自由地检索被分组的数据集中的任何值。请考虑使用排除连接。

排除连接-明确的实体

假设姓和名的索引是唯一的(明确的)， GROUP BY的替代方法是使用LEFT JOIN来筛选结果集，或者称为排除JOIN。

参见示范

升序(A-Z)

从A-Z中检索按姓排序的不同的姓

查询

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;

结果

| id | firstname | lastname |
|----|-----------|----------|
|  2 |      Bugs |    Bunny |
|  1 |      John |      Doe |

降序(Z-A)

从Z-A中检索按姓排序的不同的姓

查询

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;

结果

| id | firstname | lastname |
|----|-----------|----------|
|  2 |      Bugs |    Bunny |
|  3 |      John |  Johnson |

然后可以根据需要对结果数据进行排序。

排除连接-模糊实体

如果名字和姓氏组合不是唯一的(模糊)，并且有多行相同的值，可以通过在JOIN条件上包含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;

结果

| id | firstname | lastname |
|----|-----------|----------|
|  1 |      John |      Doe |
|  2 |      Bugs |    Bunny |

复合IN()子查询-明确的实体

对于较大的数据集，使用排除连接可能非常慢。 如果你有明确的条目，另一种方法是对MIN/MAX聚合子查询使用Composite IN()条件

示范

升序(A-Z)

查询

SELECT t1.*
FROM table_name AS t1
WHERE (t1.firstname, t1.lastname) IN(
SELECT firstname, MIN(lastname)
FROM table_name
GROUP BY firstname
)

结果

| id | firstname | lastname |
|----|-----------|----------|
|  2 |      Bugs |    Bunny |
|  1 |      John |      Doe |

降序(Z-A)

查询

SELECT t1.*
FROM table_name AS t1
WHERE (t1.firstname, t1.lastname) IN(
SELECT firstname, MAX(lastname)
FROM table_name
GROUP BY firstname
)

结果

| id | firstname | lastname |
|----|-----------|----------|
|  2 |      Bugs |    Bunny |
|  3 |      John |  Johnson |

复合IN()依赖子查询-模糊实体

通过在id列上添加带有MIN/MAX的依赖子查询，可以将相同的理论从Ambiguous Exclusion连接应用到复合IN()子查询方法。

示范

查询

SELECT t1.*
FROM table_name AS t1
WHERE t1.id IN(
SELECT MIN(id)
FROM table_name
WHERE (t1.firstname, t1.lastname) IN(
SELECT firstname, MIN(lastname)
FROM table_name
GROUP BY firstname
)
GROUP BY firstname, lastname
);

结果

| id | firstname | lastname |
|----|-----------|----------|
|  1 |      John |      Doe |
|  2 |      Bugs |    Bunny |

命令子查询

编辑

我最初使用<强>命令子查询< / >强的答案是在MySQL 5.7.5之前写的，由于ONLY_FULL_GROUP_BY的变化，它不再适用。请用上面的例子代替。

同样重要的是要注意;当ONLY_FULL_GROUP_BY被禁用(MySQL 5.7.5之前的原始行为)时，使用没有聚合函数的GROUP BY可能会产生意想不到的结果，因为MySQL可以自由地在被分组为(原文如此)的数据集中选择任何值。

这意味着ID或lastname值可以通过被检索的firstname行检索到that is not associated。

警告

在MySQL中，GROUP BY在使用ORDER BY时可能不会产生预期的结果

见测试用例

确保预期结果的最佳实现方法是使用有序子查询筛选结果集范围。

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

结果(MySQL 5.6)

| ID | first |    last |
|----|-------|---------|
|  2 |  Bugs |   Bunny |
|  3 |  John | Johnson |

比较

演示GROUP BY与ORDER BY结合使用时的意外结果

查询

SELECT * FROM table_name GROUP BY FirstName ORDER BY ID DESC

结果(MySQL 5.6)

| ID | first |  last |
|----|-------|-------|
|  2 |  Bugs | Bunny |
|  1 |  John |   Doe |
小开 
SELECT DISTINCT (column1), column2
FROM table1
GROUP BY column1
小开

请记住，在使用by和order by组时，MySQL是唯一允许在组中按和/或按块排序的数据库，这些列不是select语句的一部分。

例如: 选择column1 从表 按列2分组 按column3

排序

这将不会飞在其他数据库，如Postgres, Oracle, MSSQL等。您必须在这些数据库中执行以下操作

选择columnn1, column2, column3 从表 按列2分组 按column3

排序

只是一些信息，以防您曾经将当前的代码迁移到另一个数据库或开始在另一个数据库中工作，并尝试重用代码。

小开

如何

`SELECT
my_distinct_column,
max(col1),
max(col2),
max(col3)
...
FROM
my_table
GROUP BY
my_distinct_column`
小开

正如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;

错误1055 (42000):SELECT列表中的表达式#1不在GROUP BY子句中，并且包含未聚合的列mydatabase.table_name。id'，它不依赖于GROUP BY子句中的列;这与sql_mode=only_full_group_by不兼容

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)

如上所述，

在这种情况下，MySQL忽略每个名称组中地址值的不确定性并接受查询。如果您不关心为每个组选择非聚合列的哪个值，那么这可能很有用。ANY_VALUE()不是一个聚合函数，不像SUM()或COUNT()这样的函数。它只是抑制了不确定性的测试。


提交答案