每个组 ID 仅返回 1 条记录的 SQL 查询 [英] SQL query to return only 1 record per group ID
问题描述
我正在寻找一种方法来处理以下情况.我有一个数据库表,我只需要为表中包含的每个组 ID"返回一条记录,此外,在每个组中选择的记录应该是家庭中最年长的人.
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 Group ID Name Age
1 134 John Bowers 37
2 134 Kerri Bowers 33
3 135 John Bowers 44
4 135 Shannon Bowers 42
因此,在上面提供的示例数据中,我需要返回 ID 1 和 3,因为它们是每个组 ID 中年龄最大的人.
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.
这是针对 SQL Server 2005 数据库进行查询的.
This is being queried against a SQL Server 2005 database.
推荐答案
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
或者这个:
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:
这篇关于每个组 ID 仅返回 1 条记录的 SQL 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!