如何以水平方式分组和显示表数据?

Question

事务表:

8003001001000500801000100100100701000

您可以使用条件聚合:

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

我们不可能从你给我们的东西中判断出你真正想要的是什么，但我猜它应该是这样的:

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操作符混合在一起，而不使用括号将它们正确地分组。否则，它们将按照它们在查询中出现的顺序执行，这几乎是不正确的。