使用来自不同行的值的案例表达式

Question

我有以下表格，

WITH TMP AS (
SELECT 'XX' AS AGG, 50 AS VALUE, 0 AS MONTH
UNION ALL
SELECT 'XX' AS AGG, 150 AS VALUE, 1 AS MONTH
UNION ALL
SELECT 'XX' AS AGG, 300 AS VALUE, 2 AS MONTH
UNION ALL
SELECT 'YY' AS AGG, 25 AS VALUE, 0 AS MONTH
UNION ALL
SELECT 'YY' AS AGG, 50 AS VALUE, 1 AS MONTH
UNION ALL
SELECT 'YY' AS AGG, 75 AS VALUE, 2 AS MONTH
UNION ALL
SELECT 'ZZ' AS AGG, 500 AS VALUE, 0 AS MONTH
UNION ALL
SELECT 'ZZ' AS AGG, 600 AS VALUE, 1 AS MONTH
UNION ALL
SELECT 'ZZ' AS AGG, 700 AS VALUE, 2 AS MONTH
)
-- CASE WHEN VALUE OF 'XX' > 0 THEN VALUE OF 'ZZ' ELSE 0 END
SELECT 
*
FROM TMP
WHERE 1 = 1

我需要将以下业务逻辑应用于本月。

'xx'＆gt的值时; 0然后“ zz”的值0结束

该逻辑应映射到新表中。结果应该看起来像下表。
我想避免加入同一张桌子。使用窗口函数或类似的东西，这可能有些可能吗？这个想法是在同一SQL语句中的上述语句作为上述语句时构建几种情况。

IE	1月
500	00
600	01
700	02

Answer 1

枢轴（有条件地汇总）您的数据，然后根据表达式返回所需的值：

WITH TMP AS
    (SELECT 'XX' AS AGG,
            50 AS VALUE,
            0 AS MONTH
     UNION ALL
     SELECT 'XX' AS AGG,
            150 AS VALUE,
            1 AS MONTH
     UNION ALL
     SELECT 'XX' AS AGG,
            300 AS VALUE,
            2 AS MONTH
     UNION ALL
     SELECT 'YY' AS AGG,
            25 AS VALUE,
            0 AS MONTH
     UNION ALL
     SELECT 'YY' AS AGG,
            50 AS VALUE,
            1 AS MONTH
     UNION ALL
     SELECT 'YY' AS AGG,
            75 AS VALUE,
            2 AS MONTH
     UNION ALL
     SELECT 'ZZ' AS AGG,
            500 AS VALUE,
            0 AS MONTH
     UNION ALL
     SELECT 'ZZ' AS AGG,
            600 AS VALUE,
            1 AS MONTH
     UNION ALL
     SELECT 'ZZ' AS AGG,
            700 AS VALUE,
            2 AS MONTH),
--Solution
/*WITH */Pvt AS(
    SELECT Month,
           MAX(CASE Agg WHEN 'XX' THEN VALUE END) AS XX,
           --MAX(CASE Agg WHEN 'YY' THEN VALUE END) AS YY,--Not needed hence commented out
           MAX(CASE Agg WHEN 'ZZ' THEN VALUE END) AS ZZ
    FROM TMP
    GROUP BY MONTH)
SELECT CASE WHEN XX > 0 THEN ZZ ELSE 0 END AS IE1,
       MONTH
FROM Pvt;