SQL Server 2008 如何按百分比分组对单列值进行求和及相减运算

你可以在原有CTE的基础上按百分比分组聚合，分别提取收付款金额后做差值计算和格式拼接，完整实现代码如下：

DECLARE @BalanceTblRec TABLE 
                       (
                           NetAmount decimal(18, 3), 
                           Percentage int, 
                           [Description] nvarchar(max)
                       )
DECLARE @BalanceTblPay TABLE 
                       (
                           NetAmount decimal(18, 3), 
                           Percentage int, 
                           [Description] nvarchar(max)
                       )

INSERT INTO @BalanceTblRec 
VALUES (21, 11, 'ReceiveReceipt'),
       (20, 11, 'ReceiveReceipt'),
       (20, 10, 'ReceiveReceipt'),
       (20, 20, 'ReceiveReceipt'), 
       (10, 10, 'ReceiveReceipt')

INSERT INTO @BalanceTblPay 
VALUES (10, 11, 'PayReceipt'),
       (10, 11, 'PayReceipt'), 
       (10, 2, 'PayReceipt'),
       (5, 15, 'PayReceipt'),
       (30, 10, 'PayReceipt'),
       (20, 10, 'PayReceipt')

;WITH MaPercentage AS
( 
    SELECT
        Percentage, 
        SUM(NetAmount) AS Net, 
        'Receive' AS Flag 
    FROM
        @BalanceTblRec 
    GROUP BY
        Percentage
    UNION ALL
    SELECT 
        Percentage, 
        SUM(NetAmount) AS Net, 
        'Pay' AS Flag 
    FROM
        @BalanceTblPay 
    GROUP BY 
        Percentage
)
SELECT 
    Percentage AS Per,
    -- 按要求拼接Net字段显示格式
    LTRIM(STR(ISNULL(MAX(CASE WHEN Flag='Receive' THEN Net END),0),18,3)) 
    + CASE WHEN MAX(CASE WHEN Flag='Receive' THEN Net END) IS NOT NULL AND MAX(CASE WHEN Flag='Pay' THEN Net END) IS NOT NULL 
           THEN ' - ' + CAST(CAST(MAX(CASE WHEN Flag='Pay' THEN Net END) AS INT) AS VARCHAR)
           ELSE '' END AS Net,
    -- 根据收付款差值判断最终Flag
    CASE WHEN ISNULL(SUM(CASE WHEN Flag='Receive' THEN Net ELSE -Net END),0) >=0 
         THEN 'Receive' 
         ELSE 'Pay' END AS Flag
FROM MaPercentage
GROUP BY Percentage
-- 排序逻辑和样例输出一致：先显示Receive类百分比，再显示Pay类，同类型按百分比升序
ORDER BY CASE WHEN Flag='Receive' THEN 0 ELSE 1 END, Percentage

逻辑说明 #

• 对原有CTE的结果按Percentage分组，通过CASE表达式分别提取同一百分比下的收款总额、付款总额
• 拼接Net显示字段时，若该百分比同时存在收付款记录则拼接为收款额 - 付款额格式，仅存在单一类型记录时直接显示对应金额
• 计算收款总额减付款总额的差值，差值≥0时标记为Receive，否则标记为Pay
• 排序规则和样例输出完全匹配，最终输出结果和你要求的格式一致。

内容的提问来源于stack exchange，提问作者QTechnolog Sol