案例说明，查找每个国家的奖牌总数

Question

我在SQL Server中编写了以下查询，以便根据赢得奖牌的运动员数量来查找各国的奖牌总数

SELECT 
SUM(CASE WHEN MEDAL = 'G' THEN 1 ELSE 0 END) AS 'Gold',
SUM(CASE WHEN MEDAL = 'S' THEN 1 ELSE 0 END) AS 'Silver',
SUM(CASE WHEN MEDAL = 'B' THEN 1 ELSE 0 END) AS 'Bronze', 
COUNT(*) AS total_medals, 
TEAM
FROM 
[dbo].[commonwealth games 2022 - players won medals in cwg games 2022]
GROUP BY 
TEAM
ORDER BY 
COUNT(*) DESC

由于在曲棍球等团队运动中，每个运动员的奖牌都会计入奖牌总数，因此奖牌总数比实际情况要高得多。有没有办法修改案例陈述，或者在团队运动的情况下，使用CTE或Window函数只对单个项目的奖牌数进行一次计数。

我使用的表格有6列

ATHLETE_NAME, TEAM (country), SPORT, EVENT, MEDAL, CONTINENT

Answer 1

您可以使用CTE(或派生表(来获取DISTINCT行，然后聚合：

WITH CTE AS(
SELECT DISTINCT
TEAM,
SPORT,
EVENT,
MEDAL
FROM [dbo].[commonwealth games 2022 - players won medals in cwg games 2022]) --I really suggest a better object name
SELECT SUM(CASE WHEN MEDAL = 'G' THEN 1 ELSE 0 END) AS Gold, --Don't use literal strings for aliases
SUM(CASE WHEN MEDAL = 'S' THEN 1 ELSE 0 END) AS Silver,  --Don't use literal strings for aliases
SUM(CASE WHEN MEDAL = 'B' THEN 1 ELSE 0 END) AS Bronze, --Don't use literal strings for aliases
COUNT(*) AS total_medals,
TEAM
FROM CTE
GROUP BY TEAM
ORDER BY COUNT(*) DESC;

或者，您可以获得每个组和聚合的TOP 1：

WITH CTE AS
(SELECT TEAM,
MEDAL,
ROW_NUMBER() OVER (PARTITION BY TEAM, SPORT, EVENT, MEDAL ORDER BY ATHLETE_NAME) AS RN
FROM [dbo].[commonwealth games 2022 - players won medals in cwg games 2022]) --I really suggest a better object name
SELECT SUM(CASE WHEN MEDAL = 'G' THEN 1 ELSE 0 END) AS Gold,
SUM(CASE WHEN MEDAL = 'S' THEN 1 ELSE 0 END) AS Silver,
SUM(CASE WHEN MEDAL = 'B' THEN 1 ELSE 0 END) AS Bronze,
COUNT(*) AS total_medals,
TEAM
FROM CTE
WHERE RN = 1
GROUP BY TEAM
ORDER BY COUNT(*) DESC;