如何以水平方式对表格数据进行分组和显示？

2024-05-20 • 问答

交易表：

Client_ID	accountType	TransactionDate	TransactionType	数量
C100111001	C	1997-12-13	R	60
C100111001	C	1997-12-20	D	800
C100111001	E	1997-12-15	D	300
C100111001	E	1997-12-23	R	100
C100111002	C	1997-03-20	D	1000
C100111002	C	1997-03-25	R	40
C100111002	C	1997-05-20	D	500
C100111002	C	1997-08-20	R	80
C100111003	C	1998-12-25	D	1000
C100111003	C	1999-10-01	R	100
C100111003	C	1999-12-01	D	100
C100111003	V	1998-09-11	R	100
C100111003	V	1998-10-12	D	70
C100111003	V	1998-11-13	D	30
C100113002	C	1999-02-04	D	1000

我的查询：

select  accType,SUM(Amount) as 'Amount',year(TransDate) as 'Year'
from    Transactions
where   (year(TransDate)='1997' and TransType='D')
     or (year(TransDate)='1997' and TransType='R')
     or (year(TransDate)='1998' and TransType='D')
     or (year(TransDate)='1998' and TransType='R')
     or (year(TransDate)='1999' and TransType='D')
     or (year(TransDate)='1999' and TransType='R')
group by accType,TransType,year(Transdate)
order by year(TransDate)

我的结果表：

accType	数量	年份
C	2300.00	1997
C	180.00	1997
S	300.00	1997
S	100.00	1997
C	1000.00	1998
V	100.00	1998
V	100.00	1998
C	1100.00	1999
C	100.00	1999

所需的输出表：

accType	1997 学分	1997 借记	1998 学分	1998 借记	1999 学分	1999 借记
C	2300.0000	180.0000	1000.0000	0.0000	1100.0000	100.0000
E	300.0000	100.0000	0.0000	0.0000	0.0000	0.0000
V	0.0000	0.0000	100.0000	100.0000	0.0000	0.0000

在顶部，我的查询没有给我想要的结果。请参阅所需的输出表并帮助我。在我的结果表中，数据在我想要的水平格式的数据中按照所需的输出表显示。

我们无法从您提供给我们的信息中看出您真正要寻找的是什么，但我猜它应该是这样的：

select  AccType,SUM(Amount),year(TransDate) 
from    Transactions
where   (year(TransDate)='1997' and TransType='D')
     or (year(TransDate)='1997' and TransType='R')
     or (year(TransDate)='1998' and TransType='D')
     or (year(TransDate)='1998' and TransType='R')
     or (year(TransDate)='1999' and TransType='D')
     or (year(TransDate)='1999' and TransType='R')
group by AccType,TransType,year(Transdate)
order by year(TransDate)

正如我在评论中提到的，您不能将 AND 运算符和 OR 运算符混合在一起而不使用括号将它们正确分组。否则，它们将按照它们在查询中出现的顺序执行，这几乎永远不会正确。

您可以使用条件聚合来实现：

SELECT AccType,COALESCE(SUM(CASE WHEN YEAR(TransDate) = 1997 AND TransType = 'D' THEN Amount END),0) `1997 Credit`,COALESCE(SUM(CASE WHEN YEAR(TransDate) = 1997 AND TransType = 'R' THEN Amount END),0) `1997 Debit`,COALESCE(SUM(CASE WHEN YEAR(TransDate) = 1998 AND TransType = 'D' THEN Amount END),0) `1998 Credit`,COALESCE(SUM(CASE WHEN YEAR(TransDate) = 1998 AND TransType = 'R' THEN Amount END),0) `1998 Debit`,COALESCE(SUM(CASE WHEN YEAR(TransDate) = 1999 AND TransType = 'D' THEN Amount END),0) `1999 Credit`,COALESCE(SUM(CASE WHEN YEAR(TransDate) = 1999 AND TransType = 'R' THEN Amount END),0) `1999 Debit`
FROM Transactions
WHERE YEAR(TransDate) IN (1997,1998,1999) 
  AND TransType IN ('D','R') -- you may remove this condition if 'D' and 'R' are the only possible values
GROUP BY AccType

参见demo。

如何以水平方式对表格数据进行分组和显示？

z81452418 回答：如何以水平方式对表格数据进行分组和显示？

大家都在问