SQL子查询列条件筛选：仅保留GoldFine等列大于0的行

方法一：用CTE简化逻辑并实现过滤 #

通过公共表表达式（CTE）先计算所有所需列，再在外层添加过滤条件，既提升代码可读性，又避免重复查询带来的性能损耗：

WITH CalculatedData AS (
    SELECT 
        Ledger.Grup,
        ledger.CompanyName,
        ledger.City,
        ledger.ContactNo,
        -- 计算GoldFine：CR减DR的ConditionFine总和
        ISNULL(SUM(CASE WHEN partytype = 'GOLD' AND CrDr = 'CR' THEN ConditionFine ELSE 0 END), 0)
        - ISNULL(SUM(CASE WHEN partytype = 'GOLD' AND CrDr = 'DR' THEN ConditionFine ELSE 0 END), 0) AS GoldFine,
        -- 计算SilverFine：CR减DR的ConditionFine总和
        ISNULL(SUM(CASE WHEN partytype = 'SILVER' AND CrDr = 'CR' THEN ConditionFine ELSE 0 END), 0)
        - ISNULL(SUM(CASE WHEN partytype = 'SILVER' AND CrDr = 'DR' THEN ConditionFine ELSE 0 END), 0) AS SilverFine,
        -- 计算Amount：CR减DR的AfterTax总和
        ISNULL(SUM(CASE WHEN CrDr = 'CR' THEN AfterTax ELSE 0 END), 0)
        - ISNULL(SUM(CASE WHEN CrDr = 'DR' THEN AfterTax ELSE 0 END), 0) AS Amount
    FROM   
        tbl_Billing billing
    LEFT OUTER JOIN 
        tbl_Ledger ledger ON ledger.Ledger_ID = billing.partyid
    WHERE  
        billing.BranchID = 13
        AND Ledger.CompanyName IS NOT NULL
        AND AccountMode = 'ESTIMATE'
    GROUP BY 
        Ledger.Grup, ledger.CompanyName, ledger.City, ledger.ContactNo
)
SELECT *
FROM CalculatedData
WHERE GoldFine > 0 OR SilverFine > 0 OR Amount > 0;

方法二：嵌套子查询实现过滤 #

如果不想用CTE，可直接将原查询作为子查询，在外层添加过滤条件：

SELECT *
FROM (
    SELECT Ledger.Grup,
           ledger.CompanyName,
           ledger.City,
           ledger.ContactNo,
           (ISNULL((SELECT SUM(ConditionFine)
                    FROM tbl_Billing
                    WHERE partyid = ledger_id
                      AND partytype = 'GOLD'
                      AND CrDr = 'CR'
                      AND AccountMode = 'ESTIMATE'
                      AND BranchId = 13), 0)
               - ISNULL((SELECT SUM(ConditionFine)
                         FROM tbl_Billing
                         WHERE partyid = ledger_id
                         AND partytype = 'GOLD'
                         AND CrDr = 'DR'
                         AND AccountMode = 'ESTIMATE'
                         AND BranchId = 13), 0))
                AS GoldFine,
           (ISNULL((SELECT SUM(ConditionFine)
                    FROM tbl_Billing
                    WHERE partyid = ledger_id
                      AND partytype = 'SILVER'
                      AND CrDr = 'CR'
                      AND AccountMode = 'ESTIMATE'
                      AND BranchId = 13), 0)
               - ISNULL((SELECT SUM(ConditionFine)
                         FROM tbl_Billing
                         WHERE partyid = ledger_id
                         AND partytype = 'SILVER'
                         AND CrDr = 'DR'
                         AND AccountMode = 'ESTIMATE'
                         AND BranchId = 13), 0))
               AS SilverFine,
           (ISNULL((SELECT SUM(AfterTax)
                    FROM tbl_Billing
                    WHERE partyid = ledger_id
                      AND CrDr = 'CR'
                      AND AccountMode = 'ESTIMATE'
                      AND BranchId = 13), 0)
               - ISNULL((SELECT SUM(AfterTax)
                         FROM tbl_Billing
                         WHERE partyid = ledger_id
                         AND CrDr = 'DR'
                         AND AccountMode = 'ESTIMATE'
                         AND BranchId = 13), 0))
               AS Amount
    FROM   
        tbl_Billing billing
    LEFT OUTER JOIN 
        tbl_Ledger ledger ON ledger.Ledger_ID = billing.partyid
    WHERE  
        billing.BranchID = 13
        AND Ledger.CompanyName IS NOT NULL
        AND AccountMode = 'ESTIMATE'
    GROUP BY 
        Ledger.Grup, ledger.CompanyName, ledger.City, ledger.ContactNo
) AS SubQuery
WHERE GoldFine > 0 OR SilverFine > 0 OR Amount > 0;

关键说明 #

1. 原查询存在大量重复子查询，方法一用CASE聚合的方式，可一次性计算所有所需总和，显著提升查询性能。
2. 过滤条件GoldFine > 0 OR SilverFine > 0 OR Amount > 0直接排除三列值均为0的记录，只保留至少一个列大于0的行。
3. 原查询需添加GROUP BY子句，否则会因聚合函数的存在触发语法错误，显式分组也更符合SQL规范。

内容的提问来源于stack exchange，提问作者s.k.Soni