本文介绍了SQL按年汇总报告,寻找一种优雅的解决方案的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个包含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按年汇总报告,寻找一种优雅的解决方案的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-16 09:44