尝试将在select语句中创建的列与在select语句中指定的列相加

Question

我正在使用以下代码，并希望从前面的案例中获得Total_Bank_Amount和Prog_Variance SUMed列。

SELECT 
    b.Date, 
    SUM(p.Prog_Amount) AS Prog_Amount, 
    SUM(o.Credit_Amt) AS Outstanding, 
    CASE 
       WHEN b.Bank_Name Like '%Corp%' 
          THEN SUM(b.Credit_Amt) 
    END AS TMP_Amount,
    CASE 
       WHEN b.Bank_Name Like '%Cleo%' 
          THEN SUM(b.Credit_Amt) 
    END AS Cleo_Amount, 
    CASE 
       WHEN b.Bank_Name Like '%NY Bank%' 
          THEN SUM(b.Credit_Amt)
    END AS NY_Amount, 
    CASE 
       WHEN b.Bank_Name Like '%MA%' 
          THEN SUM(b.Credit_Amt) 
    END AS MA_Amount, 
    CASE 
       WHEN b.Bank_Name Like '%CT%' 
          THEN SUM(b.Credit_Amt) 
    END AS CT_Amount, 
    CASE 
       WHEN b.Bank_Name Like '%NY_SS%' 
          THEN SUM(b.Credit_Amt)
    END AS NY_SS_Amount, 
    CASE 
       WHEN b.Bank_Name LIKE '%VC_SS%' 
          THEN SUM(b.Credit_Amt)
    END AS VC_SS_Amount,
    (TMP_Amount + Cleo_Amount + NY_Amount + MA_Amount + CT_Amount + NY_SS_Amount + VC_SS_Amount) AS Total_Bank_Amount,
    (Prog_Amount + Outstanding - TMP_Amount - Cleo_Amount - NY_Amount - MA_Amount - CT_Amount - NY_SS_Amount - VC_SS_Amount) AS Prog_Variance
FROM 
    vw_Prog_Reference_Summary p
RIGHT JOIN 
    Bank_Detail b ON p.Bank_Reference_Number = b.Bank_Reference 
LEFT JOIN 
    vw_Outstanding_Form o ON b.Bank_Reference = o.Bank_Reference
LEFT JOIN 
    Exclusion e ON b.Bank_Reference = e.Exclude
WHERE 
    e.Exclude IS NULL
GROUP BY 
    b.Bank_Name, b.Date

问题是它们在解析时还没有创建，那么如何解决这个问题呢？研究表明我什么也得不到。谢谢。

Answer 1

遗憾的是，您不能在同一select语句中引用列别名。通常，解决此问题的方法是使核心查询成为子查询，然后引用列别名，或者您可以在计算中再次重复相同的逻辑(因此，您将在聚合计算中再次重复所有这些case和sum语句)。

下面的链接供参考，因为之前已经询问过了：Reference an alias elsewhere in the SELECT list

Answer 2

尝试如下所示：

SELECT
    (TMP_Amount + Cleo_Amount + NY_Amount + MA_Amount + CT_Amount + NY_SS_Amount + VC_SS_Amount) AS Total_Bank_Amount,
    (Prog_Amount + Outstanding - TMP_Amount - Cleo_Amount - NY_Amount - MA_Amount - CT_Amount - NY_SS_Amount - VC_SS_Amount) AS Prog_Variance
FROM (
    SELECT b.Date, Sum(p.Prog_Amount) AS Prog_Amount, Sum(o.Credit_Amt) AS Outstanding, 
        CASE WHEN b.Bank_Name Like '%Corp%' THEN SUM(b.Credit_Amt) END AS TMP_Amount,
        CASE WHEN b.Bank_Name Like '%Cleo%' THEN SUM(b.Credit_Amt) END AS Cleo_Amount, 
        CASE WHEN b.Bank_Name Like '%NY Bank%' THEN SUM(b.Credit_Amt)END AS NY_Amount, 
        CASE WHEN b.Bank_Name Like '%MA%' THEN SUM(b.Credit_Amt) END AS MA_Amount, 
        CASE WHEN b.Bank_Name Like '%CT%' THEN SUM(b.Credit_Amt) END AS CT_Amount, 
        CASE WHEN b.Bank_Name Like '%NY_SS%' THEN SUM(b.Credit_Amt)END AS NY_SS_Amount, 
        CASE WHEN b.Bank_Name Like '%VC_SS%' THEN SUM(b.Credit_Amt)END AS VC_SS_Amount
    FROM vw_Prog_Reference_Summary p
        RIGHT JOIN Bank_Detail b ON p.Bank_Reference_Number = b.Bank_Reference 
        LEFT JOIN vw_Outstanding_Form o ON b.Bank_Reference = o.Bank_Reference
        LEFT JOIN Exclusion e ON b.Bank_Reference = e.Exclude
    WHERE e.Exclude Is Null
    GROUP BY b.Bank_Name, b.Date
) t

我将旧查询移到了一个子查询中，这样您需要的值就可以使用了。我还将Total_Bank_Amount和Prog_Variance的总和移到了外部。

Answer 3

我认为您正在寻找条件聚合：

SELECT b.Date, Sum(p.Prog_Amount) AS Prog_Amount, Sum(o.Credit_Amt) AS Outstanding, 
       SUM(CASE WHEN b.Bank_Name Like '%Corp%' THEN b.Credit_Amt END) AS TMP_Amount,
      . . .
FROM . . .
GROUP BY b.Date;

也就是说，您希望将CASE作为SUM()的参数，而不是相反。

这假设(合理地)您实际上并不想为每家银行单独一行，而是只想要聚合的信息。