SQL如何去除CTE中来自变量及临时表的重复数据并合并多表结果

问题原因 #

你出现大量重复数据的核心原因是错误使用了JOIN横向拼接多个年份的结果集，而非用UNION/UNION ALL纵向合并结果。你用相同的Category字段做关联条件时，2011年的10条数据会和2012年的10条数据两两匹配，直接产生10*10=100条结果，再关联2013、2014年的数据后量级会指数级增长，自然出现大量冗余重复。

解决方案 #

1. 直接修改原有CTE写法

将多表JOIN替换为UNION ALL纵向拼接即可，四个年份的结果集字段结构完全一致，不需要关联：

WITH Furniture2011 AS (
    SELECT DISTINCT * FROM Top10CustomerByCategoryInYear('Furniture',2011,2011)
), Furniture2012 AS (
    SELECT DISTINCT * FROM Top10CustomerByCategoryInYear('Furniture',2012,2012)
), Furniture2013 AS (
    SELECT DISTINCT * FROM Top10CustomerByCategoryInYear('Furniture',2013,2013)
),  Furniture2014 AS  (
    SELECT DISTINCT * FROM Top10CustomerByCategoryInYear('Furniture',2014,2014)
)
SELECT * FROM Furniture2011
UNION ALL
SELECT * FROM Furniture2012
UNION ALL
SELECT * FROM Furniture2013
UNION ALL
SELECT * FROM Furniture2014

如果需要全局去重可以将UNION ALL替换为UNION，但你的场景是分年统计Top10，同一条记录不会出现在多个年份的结果中，UNION ALL性能更优。

2. 更简化的实现方式

你无需拆分四次调用自定义函数，直接传入2011-2014的起止年份即可一次性拿到结果：

SELECT * FROM Top10CustomerByCategoryInYear('Furniture',2011,2014)

注：如果你的需求是每年单独取该年份的Top10客户再合并，而非取2011-2014四年总销售额Top10的客户，原有自定义函数的逻辑不符合要求，可以直接用下方优化查询实现，不需要多次调用UDF，性能更好：

WITH YearlySales AS (
    SELECT 
        [Customer Name],
        [Category],
        SUM(Sales) AS Sales,
        YearOfOrderDate AS [Year]
    FROM [Project4].[dbo].[SuperStore]
    WHERE [Category] = 'Furniture' AND YearOfOrderDate BETWEEN 2011 AND 2014
    GROUP BY [Customer Name], [Category], YearOfOrderDate
), YearlySalesRank AS (
    SELECT 
        *,
        RANK() OVER (PARTITION BY [Year] ORDER BY Sales DESC) AS rank_num
    FROM YearlySales
)
SELECT [Customer Name], [Category], Sales, [Year]
FROM YearlySalesRank
WHERE rank_num <= 10

内容的提问来源于stack exchange，提问作者lukasz93