如何在SQL Server中按多列分组 [英] How to group by multiple columns in SQL Server

问题描述

我了解GROUP BY的工作原理，也了解为什么我的查询没有带来预期的结果.但是，在这种情况下，消除重复的最佳方法是什么?

I understand how GROUP BY works and I also understand why my query does not bring the results I am expecting. However, what would be the best way to eliminate duplicates in this case?

假设我们有以下表格:

城市

Id    Name
---------------------
1     Seattle
2     Los Angeles
3     San Francisco

人员

Id    Name            CityId
----------------------------
1     John Smith      1
2     Peter Taylor    1
3     Kate Elliot     1
4     Bruno Davis     2
5     Jack Brown      2
6     Bob Stewart     2
7     Tom Walker      3
8     Andrew Garcia   3
9     Kate Bauer      3

我想检索所有城市的列表，并每个城市中只有一个人.

I want to retrieve a list of all cities and just one person that lives in each city.

使用GROUP BY:

SELECT c.Id, c.Name as PersonName, p.Name as CityName
FROM City c
INNER JOIN Person p ON p.CityId = c.Id
GROUP BY c.Name, p.Name

结果:

Id    PersonName      CityName
----------------------------
1     John Smith      Seattle
1     Peter Taylor    Seattle
1     Kate Elliot     Seattle
2     Bruno Davis     Los Angeles
2     Jack Brown      Los Angeles
2     Bob Stewart     Los Angeles
3     Tom Walker      San Francisco
3     Andrew Garcia   San Francisco
3     Kate Bauer      San Francisco

使用DISTINCT:

SELECT DISTINCT c.Id, c.Name as PersonName, p.Name as CityName
FROM City c
INNER JOIN Person p ON p.CityId = c.Id

相同的结果.

请非常清楚.这是预期的结果:

Just to be very clear. This is the expected result:

Id    PersonName      CityName
----------------------------
1     John Smith      Seattle
2     Bruno Davis     Los Angeles
3     Tom Walker      San Francisco

subquery将是这种情况的唯一解决方案吗?

Would subquery be the only solution for this case?

推荐答案

这里是一个使用子查询从Person表中识别第一个匹配项"的解决方案，我将其解释为是指具有最低要求的人每个城市组的ID值.

Here is a solution which uses a subquery to identify the "first match" from the Person table, which I have interpreted to mean the person with the lowest id value in each city group.

SELECT t1.Id,
       t1.Name AS PersonName,
       t2.Name AS CityName
FROM Person t1
INNER JOIN City t2
    ON t1.CityId = t2.Id
INNER JOIN
(
    SELECT CityId, MIN(Id) AS minId
    FROM Person
    GROUP BY CityId
) t3
    ON t1.CityId = t3.CityId AND t1.Id = t3.minID

可能还有一种使用窗口函数执行此操作的方法.

There is probably also a way to do this with window functions.

这篇关于如何在SQL Server中按多列分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！