如何与列中的最大值连接表?

Question

这似乎是一个相当简单的SQL语句，但我只是没有看到它。我:

• 一个名为Groups的表。列是Id, Name, Address和一堆我们不关心的其他列。
• 一个名为People的表。列是Id, GroupId, Name, Priority和一堆我们不关心的其他列。

我想要一个所有组的列表。此列表中的每一行都需要包含组的Id、Name和Address。它还需要具有最高优先级的人员的名称。优先级也可以为NULL，如果该组中没有其他具有非NULL的人，则选择该优先级。如果优先级有任何关系，我不在乎哪个被选中，只要它是其中之一。

样本数据:

Group: (Id, Name, Address)
1, Group A, Address A
2, Group B, Address B
3, Group C, Address C
4, Group D, Address D

People: (Id, GroupId, Name, Priority)
1, 1, Alice, 39
2, 1, Bob, 22
3, 1, Craig, 88
4, 2, David, NULL
5, 2, Elise, 3
6, 3, Frank, NULL

结果应为:

1, Group A, Address A, Craig
2, Group B, Address B, Elise
3, Group C, Address C, Frank
4, Group D, Address D, NULL

我可以使用SELECT * FROM People ORDER BY Priority DESC以正确的顺序获得行，但我只需要从中获得每个GroupId的第一行。

Answer 1

另一个答案也可以，但如果你还想知道这个人以及那个人的优先级(以我的经验，这种事情很常见)，这是有用的:

SELECT Id, Name, Address, Person, Priority
FROM 
(
SELECT g.Id, g.Name, g.Address, p.Name Person, p.Priority,
row_number() OVER (PARTITION BY g.ID ORDER BY p.Priority DESC) rn
FROM [Group] g
INNER JOIN People p ON p.GroupID = g.ID
) t
WHERE rn = 1

Answer 2

使用子查询获取每个组的相关Person。

SELECT *
, (
SELECT TOP 1 Name
FROM People p
WHERE p.GroupId = g.Id
ORDER BY Priority DESC
)
FROM GROUP g;