SQL查询问题：2011-2012年Top10销售艺术家及并列处理

嘿，我来帮你一步步排查这个查询的问题，先理清楚你的需求和现有代码的问题点：

需求明确 #

你需要生成2011年7月1日到2012年6月30日期间的销售额Top10艺术家报告，要求：

• 排除视频类型的曲目（T.MediaTypeId != 3）
• 展示艺术家名称和对应的总销售额
• 如果第10名有多个艺术家销售额并列，所有并列的都要包含在内

ERD关系图 #

预期结果示例 #

你当前使用的查询代码 #

SELECT TOP(10) A.Name AS [Artist Name], SUM(I.Total) AS [Total Sales] 
FROM Artist AS A 
JOIN Album AS AL ON A.ArtistId = AL.ArtistId 
JOIN Track AS T ON AL.AlbumId = T.AlbumId 
JOIN InvoiceLine AS IL ON T.TrackId = IL.TrackId 
JOIN Invoice AS I ON I.InvoiceId = IL.InvoiceId 
WHERE T.MediaTypeId != 3 and I.InvoiceDate BETWEEN '2011-07-01' AND '2012-06-30' 
GROUP BY A.Name 
ORDER BY SUM(I.Total) DESC;

实际运行结果 #

当前遇到的两个问题 #

1. 实际结果的第2、3行和预期不符（相关数据库可自行获取）
2. 不知道怎么实现“包含第10名并列”的要求，想确认DENSE_RANK()是否有用

先解决第一个问题：结果不符的原因 #

你的查询里有个关键逻辑错误：直接用SUM(I.Total)会重复统计发票总额。

解释下：一张发票里可能包含多个曲目（对应多条InvoiceLine记录），当你把发票和曲目、艺术家关联后，同一张发票会被这张发票里所有曲目的艺术家重复计算一次。比如一张$10的发票里有3首同一艺术家的歌，你的查询会把这$10加3次，但实际上这个艺术家在这张发票里的贡献应该是这3首歌的总价（也就是InvoiceLine里UnitPrice * Quantity的总和），而不是重复累加发票的总金额。

所以正确的统计方式应该是对InvoiceLine的明细金额求和，把SUM(I.Total)改成SUM(IL.UnitPrice * IL.Quantity)，这样就能得到准确的艺术家总销售额了。

再解决第二个问题：处理第10名并列的情况 #

DENSE_RANK()完全是解决这个问题的合适工具！它会给销售额相同的艺术家分配相同的排名，而且排名不会跳过数字（比如如果有两个第2名，下一个还是第3名，而不是第4名）。

具体的实现思路是：

1. 先写一个子查询（或者CTE），计算每个艺术家的正确总销售额，同时用DENSE_RANK()按照销售额从高到低生成排名
2. 在外层查询里筛选排名≤10的记录，这样所有和第10名销售额相同的艺术家都会被包含进来

修改后的查询示例：

WITH ArtistSales AS (
    SELECT 
        A.Name AS [Artist Name],
        SUM(IL.UnitPrice * IL.Quantity) AS [Total Sales],
        DENSE_RANK() OVER(ORDER BY SUM(IL.UnitPrice * IL.Quantity) DESC) AS SalesRank
    FROM Artist AS A 
    JOIN Album AS AL ON A.ArtistId = AL.ArtistId 
    JOIN Track AS T ON AL.AlbumId = T.AlbumId 
    JOIN InvoiceLine AS IL ON T.TrackId = IL.TrackId 
    JOIN Invoice AS I ON I.InvoiceId = IL.InvoiceId 
    WHERE T.MediaTypeId != 3 
        AND I.InvoiceDate BETWEEN '2011-07-01' AND '2012-06-30' 
    GROUP BY A.Name
)
SELECT [Artist Name], [Total Sales]
FROM ArtistSales
WHERE SalesRank <= 10
ORDER BY [Total Sales] DESC;

这个查询既修复了重复计算的问题，又通过DENSE_RANK()完美处理了并列排名的需求，你可以试试运行这个版本，应该就能得到和预期一致的结果了。

内容的提问来源于stack exchange，提问作者Junaid S.