每个组 ID 仅返回 1 条记录的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1954198/
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
SQL query to return only 1 record per group ID
提问by Richard West
I'm looking for a way to handle the following scenario. I have a database table that I need to return only one record for each "group id" that is contained within the table, furthermore the record that is selected within each group should be the oldest person in the household.
我正在寻找一种方法来处理以下情况。我有一个数据库表，我只需要为表中包含的每个“组 ID”返回一条记录，此外，在每个组中选择的记录应该是家庭中最年长的人。
ID Group ID Name Age
1 134 John Bowers 37
2 134 Kerri Bowers 33
3 135 John Bowers 44
4 135 Shannon Bowers 42
So in the sample data provided above I would need ID 1 and 3 returned, as they are the oldest people within each group id.
因此，在上面提供的示例数据中，我需要返回 ID 1 和 3，因为它们是每个组 ID 中年龄最大的人。
This is being queried against a SQL Server 2005 database.
这是针对 SQL Server 2005 数据库进行查询的。
回答by Quassnoi
SELECT t.*
FROM (
SELECT DISTINCT groupid
FROM mytable
) mo
CROSS APPLY
(
SELECT TOP 1 *
FROM mytable mi
WHERE mi.groupid = mo.groupid
ORDER BY
age DESC
) t
or this:
或这个：
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY groupid ORDER BY age DESC) rn
FROM mytable
) x
WHERE x.rn = 1
This will return at most one record per group even in case of ties.
即使在平局的情况下，这也将最多返回每组一条记录。
See this article in my blog for performance comparisons of both methods:
有关两种方法的性能比较，请参阅我博客中的这篇文章：
回答by OMG Ponies
Use:
用：
SELECT DISTINCT
t.groupid,
t.name
FROM TABLE t
JOIN (SELECT t.groupid,
MAX(t.age) 'max_age'
FROM TABLE t
GROUP BY t.groupid) x ON x.groupid = t.groupid
AND x.max_age = t.age
So what if there's 2+ people with the same age for a group? It'd be better to store the birthdate rather than age - you can always calculate the birthdate for presentation.
那么如果一个团体有 2 个以上年龄相同的人呢？最好存储出生日期而不是年龄 - 您始终可以计算出生日期以进行演示。
回答by Charles Bretana
Try this (assuming Groupis synonym for Household)
试试这个（假设Group是Household 的同义词）
Select * From Table t
Where Age = (Select Max(Age)
From Table
Where GroupId = t.GroupId)
If there are two or more "oldest" people in some household (They all are the same age and there is noone else older), then this will return all of them, not just one at random.
如果某个家庭中有两个或更多“最年长”的人（他们的年龄都相同，并且没有其他人年龄更大），那么这将返回所有这些人，而不仅仅是随机返回一个。
If this is an issue, then you need to add another subquery to return an arbitrary key value for one person in that set.
如果这是一个问题，那么您需要添加另一个子查询以返回该集合中一个人的任意键值。
Select * From Table t
Where Id =
(Select Max(Id) Fom Table
Where GroupId = t.GroupId
And Age =
(Select(Max(Age) From Table
Where GroupId = t.GroupId))
回答by Chris Simmons
SELECT GroupID, Name, Age
FROM table
INNER JOIN
(
SELECT GroupID, MAX(Age) AS OLDEST
FROM table
) AS OLDESTPEOPLE
ON
table.GroupID = OLDESTPEOPLE.GroupID
AND
table.Age = OLDESTPEOPLE.OLDEST
