我有一个包含 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)
我很确定有更好、更有效的方法来编写此代码,但我无法想出正确的语法。有任何想法吗?
你可以试试这个:
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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)