急SUM的应用

[yin9184]

急SUM的应用，有如下表，字段
NO DATE NAME NUM
1 2006/01/02 铅笔 5
2 2006/01/05 鉛笔 3
3 2006/02/01 笔记本 1
4 2006/02/06 笔记本 2
5 2006/01/06 小刀 5
6 2006/02/08 铅笔 6
现要写个sql统计出如下资料，想了很久，不知道这个SUM该怎么写，NO的号不同，物品一样，怎么统计，又要安月输出

NAME 1月量 2月量 ---------12月量
铅笔 8 6 --- 0
笔记本 0 3 --- 0
小刀 0 0 ----- 0

[九斤半]

-- 简单点的，用枚举
CREATE TABLE TEST([NO] INT IDENTITY(1,1),[DATE] DATETIME,[NAME] NVARCHAR(20),[NUM] INT)
INSERT TEST([DATE],[NAME],[NUM]) SELECT '2006/01/02' ,'铅笔', 5
INSERT TEST([DATE],[NAME],[NUM]) SELECT '2006/01/05' ,'鉛笔', 3
INSERT TEST([DATE],[NAME],[NUM]) SELECT '2006/02/01' ,'笔记本', 1
INSERT TEST([DATE],[NAME],[NUM]) SELECT '2006/02/06' ,'笔记本', 2
INSERT TEST([DATE],[NAME],[NUM]) SELECT '2006/01/06' ,'小刀', 5
INSERT TEST([DATE],[NAME],[NUM]) SELECT '2006/02/08' ,'铅笔', 6
--SELECT * FROM TEST
GO

CREATE PROC PS_TEST(@YEAR NCHAR(4))
AS
BEGIN
SET NOCOUNT ON


DECLARE @START_DATE DATETIME,@END_DATE DATETIME
SELECT @START_DATE=CAST(@YEAR+'-01-01' AS DATETIME),@END_DATE=CAST(@YEAR+'-12-31' AS DATETIME)

SELECT
[NAME],
[1月量]=SUM(CASE WHEN MONTH([DATE])=1 THEN [NUM] ELSE 0 END),
[2月量]=SUM(CASE WHEN MONTH([DATE])=2 THEN [NUM] ELSE 0 END),
[3月量]=SUM(CASE WHEN MONTH([DATE])=3 THEN [NUM] ELSE 0 END),
[4月量]=SUM(CASE WHEN MONTH([DATE])=4 THEN [NUM] ELSE 0 END),
[5月量]=SUM(CASE WHEN MONTH([DATE])=5 THEN [NUM] ELSE 0 END),
[6月量]=SUM(CASE WHEN MONTH([DATE])=6 THEN [NUM] ELSE 0 END),
[7月量]=SUM(CASE WHEN MONTH([DATE])=7 THEN [NUM] ELSE 0 END),
[8月量]=SUM(CASE WHEN MONTH([DATE])=8 THEN [NUM] ELSE 0 END),
[9月量]=SUM(CASE WHEN MONTH([DATE])=9 THEN [NUM] ELSE 0 END),
[10月量]=SUM(CASE WHEN MONTH([DATE])=10 THEN [NUM] ELSE 0 END),
[11月量]=SUM(CASE WHEN MONTH([DATE])=11 THEN [NUM] ELSE 0 END),
[12月量]=SUM(CASE WHEN MONTH([DATE])=12 THEN [NUM] ELSE 0 END)
FROM TEST
WHERE [DATE]>=@START_DATE AND [DATE]<=@END_DATE
GROUP BY [NAME]


SET NOCOUNT OFF
END
GO

EXEC PS_TEST '2006'

DROP PROC PS_TEST
DROP TABLE TEST

[Awang_126]

用过程可以吗

[yin9184]

可以

[free_pop2k]

将 YiZhiNet(九斤半) 中间的代码用一个while来写出

declare @month int
declare @StrSQL varchar(1000)
select @month=1
set @strSQL='Select [name]'
while @month<=12
begin
set @StrSQL=@StrSQL + ', ' + cast(@month as varchar(2)) + '月量 =SUM(CASE WHEN MONTH([DATE])=' + cast(@month as varchar(2)) +' THEN [NUM] ELSE 0 END)'
set @month=@month +1
end
set @StrSQL=@StrSQL + ' from test'
print @StrSQL