本文介绍了SQL sum by year report，寻找优雅的解决方案的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个包含 3 列的表:ItemCode、Quantity 和 DocDate.我想以更优雅"的方式创建以下报告:
I have a table with 3 columns: ItemCode, Quantity and DocDate. I would like to create the following report in a more "elegant" way:
SELECT T0.ItemCode,
(SELECT SUM(QUANTITY) FROM MyTable T1 WHERE YEAR(T0.DocDate) = 2011 AND T0.ItemCode = T1.ItemCode) AS '2011',
(SELECT SUM(QUANTITY) FROM MyTable T1 WHERE YEAR(T0.DocDate) = 2012 AND T0.ItemCode = T1.ItemCode) AS '2012'
FROM MyTable T0
GROUP BY T0.ItemCode, YEAR(T0.DocDate)
我很确定有更好、更有效的方法来编写它，但我想不出正确的语法.有什么想法吗?
I'm pretty sure there's a better, more efficient way to write this but I can't come up with the right syntax. Any ideas?
推荐答案
你可以试试这个:
SELECT T0.ItemCode,
SUM(CASE WHEN YEAR(T0.DocDate) = 2011 THEN QUANTITY ELSE 0 END) AS '2011',
SUM(CASE WHEN YEAR(T0.DocDate) = 2012 THEN QUANTITY ELSE 0 END) AS '2012'
FROM MyTable T0
GROUP BY
T0.ItemCode
这篇关于SQL sum by year report，寻找优雅的解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！