SQL技术咨询：能否通过JOIN将多列余额合并为单列？

当前查询结果 #

Id  AccountNumber   Particular  Inst1_Balance   Inst2_Balance   Inst3_Balance   Inst4_Balance   Inst5_Balance
1   99921           Inst5       0.00            0.00            0.00            0.00            232.50
1   99921           Inst3       0.00            0.00            170.00          0.00            0.00
3   86123           Inst3       0.00            0.00            843.00          0.00            0.00    
4   76543           Inst2       0.00            123.00          0.00            0.00            0.00
5   12323           Inst4       0.00            0.00            0.00            1000.00         0.00
5   12323           Inst2       0.00            75.00           0.00            0.00            0.00
5   12323           Inst1       2765.00         0.00            0.00            0.00            0.00
7   23243           Inst5       0.00            0.00            0.00            0.00            865.00
8   43467           Inst2       0.00            435.00          0.00            0.00            0.00
9   67543           Inst3       0.00            0.00            1234.00         0.00            0.00
10  33245           Inst2       0.00            111.00          0.00            0.00            0.00
11  88881           Inst2       0.00            222.00          0.00            0.00            0.00
12  99931           Inst1       767.00          0.00            0.00            0.00            0.00
12  99931           Inst2       0.00            2345.00         0.00            0.00            0.00

目标结果 #

Id  AccountNumber   Particular  Balance
1   99921           Inst5       232.50
1   99921           Inst3       170.00
3   86123           Inst3       843.00
4   76543           Inst2       123.00
5   12323           Inst4       1000.00
5   12323           Inst2       75.00
5   12323           Inst1       2765.00
7   23243           Inst5       865.00
8   43467           Inst2       435.00
9   67543           Inst3       1234.00
10  33245           Inst2       111.00
11  88881           Inst2       222.00
12  99931           Inst1       767.00
12  99931           Inst2       2345.00

当前使用的SQL代码 #

;WITH tmptable AS  (  
    SELECT INS.CustomerId AS 'Id',
        INS.Particular AS 'Particular',
        CONCAT(CON.AccountNumber) AS 'AcctNumber',
        COALESCE(SUM(case when particular = 'Inst1' then [Bal] else 0 end),0) AS 'Inst1Bal',
        COALESCE(SUM(case when particular = 'Inst2' then [Bal] else 0 end),0) AS 'Inst2Bal',
        COALESCE(SUM(case when particular = 'Inst3' then [Bal] else 0 end),0) AS 'Inst3Bal',
        COALESCE(SUM(case when particular = 'Inst4' then [Bal] else 0 end),0) AS 'Inst4Bal',
        COALESCE(SUM(case when particular = 'Inst5' then [Bal] else 0 end),0) AS 'Inst5Bal'
FROM [Installment] INS
LEFT JOIN Customer CONS
ON INS.CustomerId = CON.Id
GROUP BY INS.CustomerId,
    CON.AccountNumber,
    INS.Particular
)
SELECT Id,
    AcctNumber,
    Particular,
    CAST(Inst1Bal AS numeric(18,2))  AS 'Inst1_Balance',
    CAST(Inst2Bal AS numeric(18,2))  AS 'Inst2_Balance',
    CAST(Inst3Bal AS numeric(18,2))  AS 'Inst3_Balance',
    CAST(Inst4Bal AS numeric(18,2))  AS 'Inst4_Balance',
    CAST(Inst5Bal AS numeric(18,2))  AS 'Inst5_Balance'
FROM tmptable

解决方案 #

方法1：直接修改现有查询（无需JOIN，更简洁） #

你的临时表已经按Particular分组，且每个分组仅对应一个非零余额列，直接用CASE表达式匹配Particular提取对应值即可：

;WITH tmptable AS  (  
    SELECT INS.CustomerId AS 'Id',
        INS.Particular AS 'Particular',
        CON.AccountNumber AS 'AcctNumber',
        COALESCE(SUM(case when particular = 'Inst1' then [Bal] else 0 end),0) AS 'Inst1Bal',
        COALESCE(SUM(case when particular = 'Inst2' then [Bal] else 0 end),0) AS 'Inst2Bal',
        COALESCE(SUM(case when particular = 'Inst3' then [Bal] else 0 end),0) AS 'Inst3Bal',
        COALESCE(SUM(case when particular = 'Inst4' then [Bal] else 0 end),0) AS 'Inst4Bal',
        COALESCE(SUM(case when particular = 'Inst5' then [Bal] else 0 end),0) AS 'Inst5Bal'
FROM [Installment] INS
LEFT JOIN Customer CON
ON INS.CustomerId = CON.Id
GROUP BY INS.CustomerId,
    CON.AccountNumber,
    INS.Particular
)
SELECT 
    Id,
    AcctNumber AS AccountNumber,
    Particular,
    CAST(
        CASE Particular
            WHEN 'Inst1' THEN Inst1Bal
            WHEN 'Inst2' THEN Inst2Bal
            WHEN 'Inst3' THEN Inst3Bal
            WHEN 'Inst4' THEN Inst4Bal
            WHEN 'Inst5' THEN Inst5Bal
            ELSE 0
        END AS numeric(18,2)
    ) AS Balance
FROM tmptable

方法2：使用JOIN实现（满足你的需求） #

构造一个包含所有Inst类型的临时表，通过JOIN匹配Particular字段后提取对应余额：

;WITH tmptable AS  (  
    SELECT INS.CustomerId AS 'Id',
        INS.Particular AS 'Particular',
        CON.AccountNumber AS 'AcctNumber',
        COALESCE(SUM(case when particular = 'Inst1' then [Bal] else 0 end),0) AS 'Inst1Bal',
        COALESCE(SUM(case when particular = 'Inst2' then [Bal] else 0 end),0) AS 'Inst2Bal',
        COALESCE(SUM(case when particular = 'Inst3' then [Bal] else 0 end),0) AS 'Inst3Bal',
        COALESCE(SUM(case when particular = 'Inst4' then [Bal] else 0 end),0) AS 'Inst4Bal',
        COALESCE(SUM(case when particular = 'Inst5' then [Bal] else 0 end),0) AS 'Inst5Bal'
FROM [Installment] INS
LEFT JOIN Customer CON
ON INS.CustomerId = CON.Id
GROUP BY INS.CustomerId,
    CON.AccountNumber,
    INS.Particular
),
inst_types AS (
    SELECT 'Inst1' AS InstType UNION ALL
    SELECT 'Inst2' UNION ALL
    SELECT 'Inst3' UNION ALL
    SELECT 'Inst4' UNION ALL
    SELECT 'Inst5'
)
SELECT 
    t.Id,
    t.AcctNumber AS AccountNumber,
    t.Particular,
    CAST(
        CASE i.InstType
            WHEN 'Inst1' THEN t.Inst1Bal
            WHEN 'Inst2' THEN t.Inst2Bal
            WHEN 'Inst3' THEN t.Inst3Bal
            WHEN 'Inst4' THEN t.Inst4Bal
            WHEN 'Inst5' THEN t.Inst5Bal
            ELSE 0
        END AS numeric(18,2)
    ) AS Balance
FROM tmptable t
JOIN inst_types i ON t.Particular = i.InstType

优化建议：直接从原始表生成目标结果 #

其实完全不需要先构建多余额列的临时表，直接按分组求和即可得到目标结果，代码更简洁高效：

SELECT 
    INS.CustomerId AS Id,
    CON.AccountNumber,
    INS.Particular,
    CAST(SUM(INS.Bal) AS numeric(18,2)) AS Balance
FROM [Installment] INS
LEFT JOIN Customer CON ON INS.CustomerId = CON.Id
GROUP BY INS.CustomerId, CON.AccountNumber, INS.Particular

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