问题描述
我了解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中按多列分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!