将Group BY结果分组

本文关键字：结果 BY Group | 更新日期: 2023-09-27 18:25:56

我有一个查询：

Select 
    (coalesce(sum(Ledger.Debit), 0) - coalesce(sum(Ledger.Credit), 0))   
    + Accounts.PreviousBalance  [Balance] 
FROM 
    Accounts
LEFT join 
    Ledger on Accounts.ID = Ledger.AccountId
Where 
    Accounts.Status = 'Active'
GROUP BY 
    Accounts.ID, Accounts.PreviousBalance

它返回所有账户的23行摘要，即每个账户的客户必须支付（-ve）和接收的金额：

Balance
=========
800655.00
1869213.50
-6365.25
1148160.00
145743.70
804225.00
157625.00
66440.00
972950.00
780063.50
646680.75
277761.00
347100.00
-70882.50
-7435.50
431940.00
1319340.00
245685.00
372400.00
158220.00
608108.00
6777029.00
1147920.00

现在我想对这个总结做一个总结。支付和接收累计价值的金额。例如：

Summary
===========
-84683.25      //sum of all negative values
19077259.45    //sum of all positive values

我是这样做的：

SELECT SUM([Balance]) as [Summary]
From 
(
    SELECT CASE WHEN [Balance] > 0 THEN 'Receieve' ELSE 'Pay' END AS 'Type', [Balance]
    From 
    (
        SELECT --Accounts.ID,
            -- ( Debit - Credit ) + Previous balance = balance
        (coalesce(sum(Ledger.Debit), 0) - coalesce(sum(Ledger.Credit), 0))   
        + Accounts.PreviousBalance  [Balance]
        FROM Accounts
        LEFT join Ledger ON Accounts.ID = Ledger.AccountId
        WHERE Accounts.Status = 'Active'
        GROUP BY Accounts.ID, Accounts.PreviousBalance
    ) as accountsSummary
) as summary
GROUP BY [Type]

但我知道这不是一个好的和优化的方式。这是一种混乱的嵌套子查询方法。必须有一个更干净或更好的方法来做到这一点。如何用更好的方法实现这一点？

将Group BY结果分组

试试看：

WITH Balances
AS (
    SELECT 
        (coalesce(sum(Ledger.Debit), 0) - coalesce(sum(Ledger.Credit), 0)) + Accounts.PreviousBalance  [Balance] 
    FROM 
        Accounts
    LEFT join 
        Ledger on Accounts.ID = Ledger.AccountId
    Where 
        Accounts.Status = 'Active'
    GROUP BY 
        Accounts.ID, Accounts.PreviousBalance
),
Receipts AS (
    SELECT SUM(Balance) Balance
    FROM Balances
    WHERE Balance > 0
),
Payments AS (
    SELECT SUM(Balance) Balance
    FROM Balances
    WHERE Balance < 0
)    
SELECT Balance FROM Receipts 
UNION 
SELECT Balance FROM Payments

在外部查询中，只需区分group by中的正值和负值，这就是您所需要的

WITH Balances
AS (
    SELECT 
        (coalesce(sum(Ledger.Debit), 0) - coalesce(sum(Ledger.Credit), 0)) + Accounts.PreviousBalance  [Balance] 
    FROM 
        Accounts
    LEFT join 
        Ledger on Accounts.ID = Ledger.AccountId
    Where 
        Accounts.Status = 'Active'
    GROUP BY 
        Accounts.ID, Accounts.PreviousBalance
),
select SUM(Balance)
from Balances
group by case when Balance < 0 then 1 else 0 end

为什么不使用LINQ。首先使用SQL获取整个数据紧缩。然后使用c#（使用LINQ）对其进行查询。这会容易得多。

相关文章：