每个组 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:

• SQL Server: 选择分组最大的记录

这篇关于每个组 ID 仅返回 1 条记录的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！