急SUM的应用
急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
[解决办法]
将 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
