我想按年来查每个分类对应每个月的总合,错在什么地方?

[qfljm]

CREATE TABLE #ProName(tClassID INT,tDate DATETIME,tNum INT)
INSERT INTO #ProName SELECT 1,'2005-03-02',12
INSERT INTO #ProName SELECT 2,'2005-03-03',44
INSERT INTO #ProName SELECT 3,'2005-03-30',44
INSERT INTO #ProName SELECT 1,'2005-05-02',12
INSERT INTO #ProName SELECT 2,'2005-05-03',44
INSERT INTO #ProName SELECT 3,'2005-05-30',44
INSERT INTO #ProName SELECT 1,'2005-04-02',330
INSERT INTO #ProName SELECT 2,'2005-04-03',440
INSERT INTO #ProName SELECT 3,'2005-06-30',664

Select
版块=tClassID,
月份=MONTH(tDate),
月总计=SUM(tNum)
From #ProName
WHERE YEAR(tDate)='2005'
GROUP BY tClassID, MONTH(tDate)

我这个什么地方错了.?
我想按年来查每个分类对应每个月的总合

[qfljm]

SELECT
tClassID,
JAN = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=1),
FRI = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=2),
MAR = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=3),
APR = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=4),
MAY = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=5),
JUE = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=6),
JUL = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=7),
AGU = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=8),
SEP = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=9),
OCT = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=10),
NOV = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=11),
DEC = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1 AND MONTH(tDate)=12),
CON = (SELECT SUM(tNum) FROM #ProName1 WHERE tClassID=1)
FROM #ProName1
WHERE YEAR(tDate)=2005 And tClassID=1
GROUP BY tClassID

我要得到这样的结果,,,,能写简单点吗?

[Jane_64]

declare @s varchar(8000)
set @s='select 版块=tClassID'
select @s=@s+','+quotename(convert(varchar,dmonth)+'月份')+'=SUM(case when MONTH(tDate)='+convert(varchar,dmonth)+' then tNum else 0 end)'
From (select distinct MONTH(tDate) dmonth from #ProName WHERE YEAR(tDate)='2005') a
select @s=@s+' From #ProName WHERE YEAR(tDate)=''2005'' GROUP BY tClassID'
exec(@s)
/*
版块 3月份 4月份 5月份 6月份
1 12 330 12 0
2 44 440 44 0
3 44 0 44 664
*/

[zhangaidi]

没错,鉴定

[Teng_s2000]

select * from #proname
pivot (sum(tnum) for tdate in ([2005-03-02],[2005-03-03])) as a

结果：
tclassid 2005-03-02 2005-03-03
1 12 NULL
2 NULL 44
3 NULL NULL

但是得出每个月的还没想出来
pivot是sql2005的函数

[Teng_s2000]

select tclassid,month(tdate) as M,tnum into #proname1 from #proname
go

select * from #proname1
pivot (sum(tnum) for M in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) as a

用一个临时表可以实现

[qfljm]

晕..我没有
pivot
我的是SQL2000

[barble]

没错啊。。

[WangZWang]

执行没错，但你要实现的结果是否对? 那要看你的需求了。

[子陌红尘]

确实没错

[wgsasd311]

楼主语句没有错.

[-狙击手-]

没错

[Teng_s2000]

这是一个行列转化的问题，在sql2000中确实很麻烦的。

[sxlcom]

CREATE TABLE #ProName(tClassID INT,tDate DATETIME,tNum INT)
INSERT INTO #ProName SELECT 1,'2005-03-02',12
INSERT INTO #ProName SELECT 2,'2005-03-03',44
INSERT INTO #ProName SELECT 3,'2005-03-30',44
INSERT INTO #ProName SELECT 1,'2005-05-02',12
INSERT INTO #ProName SELECT 2,'2005-05-03',44
INSERT INTO #ProName SELECT 3,'2005-05-30',44
INSERT INTO #ProName SELECT 1,'2005-04-02',330
INSERT INTO #ProName SELECT 2,'2005-04-03',440
INSERT INTO #ProName SELECT 3,'2005-06-30',664

SELECT top 1
tClassID,
JAN = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=1),
FRI = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=2),
MAR = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=3),
APR = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=4),
MAY = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=5),
JUE = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=6),
JUL = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=7),
AGU = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=8),
SEP = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=9),
OCT = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=10),
NOV = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=11),
DEC = (SELECT SUM(tNum) FROM #ProName WHERE MONTH(tDate)=12),
CON = (SELECT SUM(tNum) FROM #ProName )
FROM #ProName
WHERE YEAR(tDate)=2005
GROUP BY tClassID