mySQL选择一个不同的列，以及相应的其他列

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

I want to select DISTINCT results from the FirstName column, but I need the corresponding ID and LastName.

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

The result set needs to show only one John, but with an ID of 1 and a LastName of Doe.

结果集只需要显示一个John，但ID为1和Doe的姓。

10 个解决方案

#1

try this query

试试这个查询

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

#2

The DISTINCT keyword doesn't really work the way you're expecting it to. When you use SELECT DISTINCT col1, col2, col3 you are in fact selecting all unique {col1, col2, col3} tuples.

独特的关键字并不是你期望的那样。当您使用选择不同的col1、col2、col3时，您实际上是在选择所有唯一的{col1, col2, col3}元组。

#3

EDIT

编辑

The original answer was written prior to MySQL 5.7.5, which no longer applies due to the default changes with ONLY_FULL_GROUP_BY. It is also important to note, that when ONLY_FULL_GROUP_BY is disabled, the use of GROUP BY without an aggregate function will yield unexpected results, as MySQL is free to choose ANY value within the data set being grouped [sic].

最初的答案是在MySQL 5.7.5之前编写的，由于ONLY_FULL_GROUP_BY的默认更改，因此不再适用。还需要注意的是，当禁用了ONLY_FULL_GROUP_BY时，使用没有聚合函数的GROUP BY将产生意外结果，因为MySQL可以*地选择正在分组的数据集中的任何值[sic]。

Assuming the firstname and lastname are uniquely indexed, an alternative to GROUP BY is to sort using a LEFT JOIN to filter the result set. See Demonstration

假设firstname和lastname是唯一索引的，那么GROUP BY的另一种替代方法是使用左连接对结果集进行排序

To retrieve the distinct firstname ordered by lastname in descending order (Z-A)

以降序(za)检索lastname排序的不同的firstname

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 |

To retrieve the distinct firstname ordered by lastname in ascending order (A-Z)

要检索按姓按升序排列的不同的firstname (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;

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

You can then order the resulting data as desired.

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

If the first and last name combination are not unique and you have multiple rows of the same values, you can filter the result set by including an OR condition on the join to choose a specific id. See the demonstration.

如果第一个和最后一个名称组合不是唯一的，并且您有多行相同的值，您可以通过在连接中包含OR条件来筛选结果集，以选择特定的id。

table_name data:

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 |

---

WARNING

警告

With MySQL GROUP BY will not always yield the expected results when used with ORDER BY See: Test Case Example.

使用MySQL GROUP BY时，在使用ORDER BY时并不总是会产生预期的结果，请参见:测试用例示例。

The best method of implementation to ensure expected results is to filter the result set scope using a Subquery like so.

确保预期结果的最佳实现方法是使用这样的子查询过滤结果集范围。

table_name data:

table_name数据:

(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

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

Versus

与

SELECT * FROM table_name GROUP BY FirstName ORDER BY ID DESC

#Results
| ID | first |  last |
|----|-------|-------|
|  2 |  Bugs | Bunny |
|  1 |  John |   Doe |

#4

SELECT ID,LastName 
From TABLE_NAME 
GROUP BY FirstName 
HAVING COUNT(*) >=1

#5

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.

否则，您可能需要使用临时表。

#6

SELECT firstName, ID, LastName from tableName GROUP BY firstName

#7

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.

记住，当使用by和order by组时，MySQL是惟一允许在group by和/或order中使用不属于select语句的列的数据库。

So for example: select column1 from table group by column2 order by column3

例如:从表组中选择column1，用column2阶和column3表示

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

从表组中选择column1、column2、column3，然后按照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.

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

#8

SELECT DISTINCT(firstName), ID, LastName from tableName GROUP BY firstName

Would be the best bet IMO.

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

#9

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

#10

select distinct (column1), column2
from table1
group by column1