如何通过SQL透视部署历史数据，查询各应用多环境最新版本及匹配情况

原查询错误原因 #

• 缺少SELECT后的输出字段列表，仅写top 500语法不合法
• PIVOT聚合函数使用了不存在的Submitted字段，且version是字符串类型，不能用AVG数值聚合函数
• 没有提前筛选每个应用在对应环境的最新部署记录，直接全量转PIVOT会得到错误的聚合结果

正确查询写法（兼容多数SQL方言，包括SQL Server、MySQL、PostgreSQL等） #

用条件聚合实现更直观，逻辑清晰不易出错：

SELECT 
    Application_Name,
    MAX(CASE WHEN Environment = 'QA1' THEN Version END) AS QA1_Latest_Version,
    MAX(CASE WHEN Environment = 'QA2' THEN Version END) AS QA2_Latest_Version,
    CASE 
        WHEN MAX(CASE WHEN Environment = 'QA1' THEN Version END) = MAX(CASE WHEN Environment = 'QA2' THEN Version END) 
        THEN 1 
        ELSE 0 
    END AS Version_Match_Flag
FROM (
    -- 子查询：给每个应用+环境的部署记录按时间倒序排名，排名1为最新部署
    SELECT 
        Application_Name,
        Environment,
        Version,
        ROW_NUMBER() OVER (PARTITION BY Application_Name, Environment ORDER BY deployed_date DESC) AS rn
    FROM Deployment_History
    WHERE Environment IN ('QA1', 'QA2') -- 过滤无关环境减少数据扫描
) t
WHERE rn = 1 -- 仅保留每个应用每个环境的最新部署记录
GROUP BY Application_Name
-- 如需取前500条，可在第一行SELECT后加TOP 500

SQL Server 下的PIVOT实现版本 #

如果坚持用PIVOT语法，可以参考以下写法：

SELECT 
    Application_Name,
    [QA1] AS QA1_Latest_Version,
    [QA2] AS QA2_Latest_Version,
    CASE WHEN [QA1] = [QA2] THEN 1 ELSE 0 END AS Version_Match_Flag
FROM (
    SELECT 
        Application_Name,
        Environment,
        Version
    FROM (
        SELECT 
            Application_Name,
            Environment,
            Version,
            ROW_NUMBER() OVER (PARTITION BY Application_Name, Environment ORDER BY deployed_date DESC) AS rn
        FROM Deployment_History
        WHERE Environment IN ('QA1', 'QA2')
    ) t
    WHERE rn = 1
) AS SourceTable
PIVOT (
    MAX(Version) -- 单条记录下MAX聚合无副作用，兼容字符串类型
    FOR Environment IN ([QA1], [QA2])
) AS PivotTable

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