如何编写SQL实现基于年份的动态列，查询指定年份TOP客户历年销售数据

以下实现基于你原SQL使用的SQL Server语法适配，如需适配其他数据库可对应调整语法细节：

固定逻辑实现（无需动态拼接，适用固定取目标年及前两年的场景） #

-- 定义查询的目标年份，修改此处即可切换查询年份
DECLARE @TargetYear INT = 2021

-- 第一步：筛选出目标年份销售额TOP10的客户名单
WITH Top10Customers AS (
    SELECT TOP 10 
        T0.Cardname
    FROM OINV T0 
    INNER JOIN Inv1 T1 ON T0.Docentry = T1.DocEntry  
    WHERE YEAR(T0.docdate) = @TargetYear
    GROUP BY T0.Cardname 
    ORDER BY SUM(T1.LineTotal) DESC
)
-- 第二步：统计上述客户三年的销售额，行转列输出
SELECT 
    Cardname,
    SUM(CASE WHEN YEAR(T0.docdate) = @TargetYear THEN T1.LineTotal ELSE 0 END) AS [销售额_2021],
    SUM(CASE WHEN YEAR(T0.docdate) = @TargetYear - 1 THEN T1.LineTotal ELSE 0 END) AS [销售额_2020],
    SUM(CASE WHEN YEAR(T0.docdate) = @TargetYear - 2 THEN T1.LineTotal ELSE 0 END) AS [销售额_2019]
FROM OINV T0
INNER JOIN Inv1 T1 ON T0.Docentry = T1.DocEntry
WHERE 
    Cardname IN (SELECT Cardname FROM Top10Customers)
    AND YEAR(T0.docdate) BETWEEN @TargetYear - 2 AND @TargetYear
GROUP BY Cardname
ORDER BY [销售额_2021] DESC

动态列名实现（修改目标年份自动生成对应列名） #

如果需要修改@TargetYear后列名自动匹配对应年份，使用动态SQL实现：

DECLARE @TargetYear INT = 2021
DECLARE @SQL NVARCHAR(MAX)

SET @SQL = N'
WITH Top10Customers AS (
    SELECT TOP 10 
        T0.Cardname
    FROM OINV T0 
    INNER JOIN Inv1 T1 ON T0.Docentry = T1.DocEntry  
    WHERE YEAR(T0.docdate) = ' + CAST(@TargetYear AS NVARCHAR(4)) + N'
    GROUP BY T0.Cardname 
    ORDER BY SUM(T1.LineTotal) DESC
)
SELECT 
    Cardname,
    SUM(CASE WHEN YEAR(T0.docdate) = ' + CAST(@TargetYear AS NVARCHAR(4)) + N' THEN T1.LineTotal ELSE 0 END) AS [销售额_' + CAST(@TargetYear AS NVARCHAR(4)) + N'],
    SUM(CASE WHEN YEAR(T0.docdate) = ' + CAST(@TargetYear - 1 AS NVARCHAR(4)) + N' THEN T1.LineTotal ELSE 0 END) AS [销售额_' + CAST(@TargetYear - 1 AS NVARCHAR(4)) + N'],
    SUM(CASE WHEN YEAR(T0.docdate) = ' + CAST(@TargetYear - 2 AS NVARCHAR(4)) + N' THEN T1.LineTotal ELSE 0 END) AS [销售额_' + CAST(@TargetYear - 2 AS NVARCHAR(4)) + N']
FROM OINV T0
INNER JOIN Inv1 T1 ON T0.Docentry = T1.DocEntry
WHERE 
    Cardname IN (SELECT Cardname FROM Top10Customers)
    AND YEAR(T0.docdate) BETWEEN ' + CAST(@TargetYear - 2 AS NVARCHAR(4)) + N' AND ' + CAST(@TargetYear AS NVARCHAR(4)) + N'
GROUP BY Cardname
ORDER BY [销售额_' + CAST(@TargetYear AS NVARCHAR(4)) + N'] DESC
'

EXEC sp_executesql @SQL

逻辑说明 #

• 先用CTE提前筛选出目标年份TOP10客户，避免全表统计所有客户数据，大幅提升查询效率
• 通过条件聚合CASE WHEN + SUM实现行转列，把不同年份的销售额汇总到对应独立列
• 动态SQL版本仅需修改@TargetYear的取值，列名会自动生成对应年份标识，无需手动调整代码
• 若使用MySQL等不支持TOP语法的数据库，将TOP 10改为LIMIT 10放在对应查询末尾即可，整体逻辑通用

内容的提问来源于stack exchange，提问作者Vigna Hari Karthik