如何在三个表上使用 MAX 和案例函数

Question

我使用 Max 和 Case 函数连接了三个表。

当我使用两个表时，它工作正常，但是当我添加第三个表时，我收到此错误。

不明确的列名称"月"。

SELECT  Category ,
        KPI ,
        TDTargetValue ,
        MAX(CASE WHEN Month = 'Jan' THEN Input
            END) Jan ,
        MAX(CASE WHEN Month = 'Feb' THEN Input
            END) Feb ,
        MAX(CASE WHEN Month = 'Mar' THEN Input
            END) Mar ,
        MAX(CASE WHEN Month = 'Apr' THEN Input
            END) Apr ,
        MAX(CASE WHEN Month = 'May' THEN Input
            END) May ,
        MAX(CASE WHEN Month = 'Jun' THEN Input
            END) Jun ,
        MAX(CASE WHEN Month = 'Jul' THEN Input
            END) Jul ,
        MAX(CASE WHEN Month = 'Aug' THEN Input
            END) Aug ,
        MAX(CASE WHEN Month = 'Sep' THEN Input
            END) Sep ,
        MAX(CASE WHEN Month = 'Oct' THEN Input
            END) Oct ,
        MAX(CASE WHEN Month = 'Nov' THEN Input
            END) Nov ,
        MAX(CASE WHEN Month = 'Dec' THEN Input
            END) Dec
FROM    [NEWSEMAKPI].[dbo].[NewCriteria] NC
        INNER JOIN ( SELECT *
                     FROM   [NEWSEMAKPI].[dbo].[UpdateData]
                     WHERE  PeriodId = '1'
                   ) UD ON UD.Cid = NC.Id
        LEFT JOIN ( SELECT  *
                    FROM    [NEWSEMAKPI].[dbo].[TargetData]
                  ) TD ON UD.Cid = TD.CId
WHERE   NC.Grade = 'A'
        AND IsActived = '0'
GROUP BY Category ,
        KPI ,
        TDTargetValue
ORDER BY 1

任何帮助将不胜感激。

Answer 1

也为列添加alias月...

假设NewCriteria表具有列Month

SELECT  Category ,
        KPI ,
        TDTargetValue ,
        MAX(CASE WHEN NC.Month = 'Jan' THEN Input
            END) Jan ,
        MAX(CASE WHEN NC.Month = 'Feb' THEN Input
            END) Feb ,
        MAX(CASE WHEN NC.Month = 'Mar' THEN Input
            END) Mar ,
        MAX(CASE WHEN NC.Month = 'Apr' THEN Input
            END) Apr ,
        MAX(CASE WHEN NC.Month = 'May' THEN Input
            END) May ,
        MAX(CASE WHEN NC.Month = 'Jun' THEN Input
            END) Jun ,
        MAX(CASE WHEN NC.Month = 'Jul' THEN Input
            END) Jul ,
        MAX(CASE WHEN NC.Month = 'Aug' THEN Input
            END) Aug ,
        MAX(CASE WHEN NC.Month = 'Sep' THEN Input
            END) Sep ,
        MAX(CASE WHEN NC.Month = 'Oct' THEN Input
            END) Oct ,
        MAX(CASE WHEN NC.Month = 'Nov' THEN Input
            END) Nov ,
        MAX(CASE WHEN NC.Month = 'Dec' THEN Input
            END) Dec
FROM    [NEWSEMAKPI].[dbo].[NewCriteria] NC
        INNER JOIN ( SELECT *
                     FROM   [NEWSEMAKPI].[dbo].[UpdateData]
                     WHERE  PeriodId = '1'
                   ) UD ON UD.Cid = NC.Id
        LEFT JOIN ( SELECT  *
                    FROM    [NEWSEMAKPI].[dbo].[TargetData]
                  ) TD ON UD.Cid = TD.CId
WHERE   NC.Grade = 'A'
        AND IsActived = '0'
GROUP BY Category ,
        KPI ,
        TDTargetValue
ORDER BY 1