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

qfljm 2006-02-22 11:45:40
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)

我这个什么地方错了.?
我想按年来查每个分类对应每个月的总合
...全文
110 点赞 收藏 13
写回复
13 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
sxlcom 2006-02-23
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
回复
Teng_s2000 2006-02-23
这是一个行列转化的问题,在sql2000中确实很麻烦的。
回复
-狙击手- 2006-02-22
没错
回复
wgsasd311 2006-02-22
楼主语句没有错.
回复
子陌红尘 2006-02-22
确实没错
回复
WangZWang 2006-02-22
执行没错,但你要实现的结果是否对? 那要看你的需求了。
回复
barble 2006-02-22
没错啊。。
回复
qfljm 2006-02-22
晕..我没有
pivot
我的是SQL2000
回复
Teng_s2000 2006-02-22
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

用一个临时表可以实现
回复
Teng_s2000 2006-02-22
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的函数
回复
zhangaidi 2006-02-22
没错,鉴定
回复
Jane_64 2006-02-22
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
*/
回复
qfljm 2006-02-22
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

我要得到这样的结果,,,,能写简单点吗?
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2006-02-22 11:45
社区公告
暂无公告