我有下表
Years Months Credit Debit ProfitandLoss Symbol
2019 Jan 10000 2000 1000 P
2019 Aug 8000 1000 -10922 L
2019 May 5000 3000 2000 P
2020 Feb 10000 5000 800 P
2020 Apr 1000 6000 0 N
2020 Oct 2000 1000 2000 P
2021 Jan 6000 8000 -600 L
2021 Mar 2000 3000 1400 P
2021 Nov 2000 2000 0 N
在這里，我需要在一個結果表中計算總貸方，總借方，總損益和總利潤，總虧損，總計數。
我已經嘗試過，但無法根據年份獲得 CountOfProfit、CountOfLoss 和 CountNothing。
select Years,
SUM(credit) as [Total Credit],
SUM(debit) as totaldebit,
COUNT(Symbol) as totalcredit,
(select COUNT(Symbol) from Yearly where Symbol='P') as CountofProfit,
(select COUNT(Symbol) from Yearly where Symbol='L') as CountofLoss,
(select COUNT(Symbol) from Yearly where Symbol='N')as CountNothing
from Yearly
group by Years
我的結果表應該像
Years TotalCredit TotalDebit TotalProfitandLoss CountOfProfit CountofLoss CountofNothing
2019 23000 7000 -7022 2 1 0
2020 13000 12000 2800 2 0 1
2021 10000 13000 800 1 1 1
uj5u.com熱心網友回復：
您需要使用條件聚合。這是通過一個CASE運算式來實作的：
SELECT Years,
SUM(Credit) AS TotalCredit,
SUM(Debit) AS TotalDebit,
SUM(ProfitandLoss) AS TotalProfitAndLoss,
COUNT(CASE Symbol WHEN 'P' THEN 1 END) AS Profits,
COUNT(CASE Symbol WHEN 'L' THEN 1 END) AS Losses,
COUNT(CASE Symbol WHEN 'N' THEN 1 END) AS Nothings
FROM (VALUES(2019,'Jan',10000,2000, 1000 ,'P'),
(2019,'Aug',8000 ,1000,-10922,'L'),
(2019,'May',5000 ,3000, 2000 ,'P'),
(2020,'Feb',10000,5000, 800 ,'P'),
(2020,'Apr',1000 ,6000, 0 ,'N'),
(2020,'Oct',2000 ,1000, 2000 ,'P'),
(2021,'Jan',6000 ,8000, -600 ,'L'),
(2021,'Mar',2000 ,3000, 1400 ,'P'),
(2021,'Nov',2000 ,2000, 0 ,'N'))V(Years,Months,Credit,Debit,ProfitandLoss,Symbol)
GROUP BY Years
ORDER BY Years;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/377482.html
標籤：sql sql-server 查询语句 子查询
上一篇：將T-SQL陳述句轉換為JavaScriptRegExp時有哪些問題
下一篇：列作為值/SQL中的非透視表