SQL 年度总和报告，寻找优雅的解决方案

Question

我有一个包含 3 列的表:ItemCode、Quantity 和 DocDate。我想以更“优雅”的方式创建以下报告:

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)

我很确定有更好、更有效的方法来编写此代码，但我无法想出正确的语法。有什么想法吗？

Answer 1

你可以试试这个:

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