--不知道你是取max还是sum select stuid , type , max([1月]) [1月] , ... max([12月]) [12月] from tb group by stuid , type select stuid , type , sum([1月]) [1月] , ... sum([12月]) [12月] from tb group by stuid , type
select stuid,type, sum(1月) as 1月,sum(2月) as 2月,sum(3月) as 3月, sum(4月) as 4月,sum(5月) as 5月, sum(6月) as 6月,sum(7月) as 7月,sum(8月) as 8月, sum(9月) as 9月,sum(10月) as 10月, sum(11月) as 11,sum(12月) as 12 月 from table group by stuid,type
这是我自己写的sql,但不是我想要的效果, SELECT studid, type ,[1月]=case when month(publishTime)=1 then count(*) else 0 end ,[2月]=case when month(publishTime)=2 then count(*) else 0 end ,[3月]=case when month(publishTime)=3 then count(*)else 0 end ,[4月]=case when month(publishTime)=2 then count(*) else 0 end ,[5月]=case when month(publishTime)=2 then count(*) else 0 end ,[6月]=case when month(publishTime)=2 then count(*) else 0 end ,[7月]=case when month(publishTime)=2 then count(*) else 0 end ,[8月]=case when month(publishTime)=8 then count(*)else 0 end ,[9月]=case when month(publishTime)=9 then count(*)else 0 end ,[10月]=case when month(publishTime)=10 then count(*)else 0 end ,[11月]=case when month(publishTime)=2 then count(*) else 0 end ,[12月]=case when month(publishTime)=2 then count(*) else 0 endfrom AnsProc where year(publishTime)=2008 group by studid,type,month(publishTime)
SELECT studid, type ,[1月]=sum(case when month(publishTime)=1 then count(*) else 0 end) ,[2月]=sum(case when month(publishTime)=2 then count(*) else 0 end) ,[3月]=sum(case when month(publishTime)=3 then count(*)else 0 end) ,[4月]=sum(case when month(publishTime)=2 then count(*) else 0 end) ,[5月]=sum(case when month(publishTime)=2 then count(*) else 0 end) ,[6月]=sum(case when month(publishTime)=2 then count(*) else 0 end) ,[7月]=sum(case when month(publishTime)=2 then count(*) else 0 end) ,[8月]=sum(case when month(publishTime)=8 then count(*)else 0 end) ,[9月]=sum(case when month(publishTime)=9 then count(*)else 0 end) ,[10月]=sum(case when month(publishTime)=10 then count(*)else 0 end) ,[11月]=sum(case when month(publishTime)=2 then count(*) else 0 end) ,[12月]=sum(case when month(publishTime)=2 then count(*) else 0 end)from AnsProc where year(publishTime)=2008 group by studid,type
SELECT studid, type ,[1月]=sum(case when month(publishTime)=1 then count(*) else 0 end) ,[2月]=sum(case when month(publishTime)=2 then count(*) else 0 end) ,[3月]=sum(case when month(publishTime)=3 then count(*)else 0 end) ,[4月]=sum(case when month(publishTime)=4 then count(*) else 0 end) ,[5月]=sum(case when month(publishTime)=5 then count(*) else 0 end) ,[6月]=sum(case when month(publishTime)=6 then count(*) else 0 end) ,[7月]=sum(case when month(publishTime)=7 then count(*) else 0 end) ,[8月]=sum(case when month(publishTime)=8 then count(*)else 0 end) ,[9月]=sum(case when month(publishTime)=9 then count(*)else 0 end) ,[10月]=sum(case when month(publishTime)=10 then count(*)else 0 end) ,[11月]=sum(case when month(publishTime)=11 then count(*) else 0 end) ,[12月]=sum(case when month(publishTime)=12 then count(*) else 0 end) from AnsProc where year(publishTime)=2008 group by studid,type
正确语句是这样的,只是不知道还有没有简洁的 SELECT studid, type ,[1月]=count(case when month(publishTime)=1 then type end) ,[2月]=count(case when month(publishTime)=2 then type end) ,[3月]=count(case when month(publishTime)=3 then type end) ,[4月]=count(case when month(publishTime)=4 then type end) ,[5月]=count(case when month(publishTime)=5 then type end) ,[6月]=count(case when month(publishTime)=6 then type end) ,[7月]=count(case when month(publishTime)=7 then type end) ,[8月]=count(case when month(publishTime)=8 then type end) ,[9月]=count(case when month(publishTime)=9 then type end) ,[10月]=count(case when month(publishTime)=10 then type end) ,[11月]=count(case when month(publishTime)=11 then type end) ,[12月]=count(case when month(publishTime)=12 then type end) from AnsProc where year(publishTime)=2008 group by type,studid
select stuid , type , max([1月]) [1月] , ... max([12月]) [12月] from tb group by stuid , type
select stuid , type , sum([1月]) [1月] , ... sum([12月]) [12月] from tb group by stuid , type
数据表中的字段如下:
studid: int类型
type: bit类型
time: smalldatetime类型
sum(4月) as 4月,sum(5月) as 5月,
sum(6月) as 6月,sum(7月) as 7月,sum(8月) as 8月,
sum(9月) as 9月,sum(10月) as 10月,
sum(11月) as 11,sum(12月) as 12 月
from table
group by stuid,type
SELECT studid, type
,[1月]=case when month(publishTime)=1 then count(*) else 0 end
,[2月]=case when month(publishTime)=2 then count(*) else 0 end
,[3月]=case when month(publishTime)=3 then count(*)else 0 end
,[4月]=case when month(publishTime)=2 then count(*) else 0 end
,[5月]=case when month(publishTime)=2 then count(*) else 0 end
,[6月]=case when month(publishTime)=2 then count(*) else 0 end
,[7月]=case when month(publishTime)=2 then count(*) else 0 end
,[8月]=case when month(publishTime)=8 then count(*)else 0 end
,[9月]=case when month(publishTime)=9 then count(*)else 0 end
,[10月]=case when month(publishTime)=10 then count(*)else 0 end
,[11月]=case when month(publishTime)=2 then count(*) else 0 end
,[12月]=case when month(publishTime)=2 then count(*) else 0 endfrom AnsProc where year(publishTime)=2008 group by studid,type,month(publishTime)
INSERT @TB
SELECT 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2, 0, 0, 0 UNION ALL
SELECT 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 3, 0, 0 UNION ALL
SELECT 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 6, 0, 0 UNION ALL
SELECT 2, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0 UNION ALL
SELECT 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 4, 0, 0 UNION ALL
SELECT 2, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0 UNION ALL
SELECT 2, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 3, 0, 0
SELECT stuid,type,max([1月]),max([2月]),max([3月]),max([4月]),max([5月]),max([6月]),max([7月]),max([8月]),max([9月]),max([10月]),max([11月]),max([12月])
FROM @TB GROUP BY stuid,type
order by stuid,type
/*
stuid type 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 0 0 0 0 0 0 0 0 0 2 3 0 0
1 1 0 0 0 0 0 0 0 0 0 6 0 0
2 0 0 0 0 0 0 0 0 1 0 4 0 0
2 1 0 0 0 0 0 0 0 0 1 3 0 0
*/
studid int型
type bit型
publishTime smalldatetime型
这是我以前写的sql , 想要的效果是
stuid type 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
1 0 0 0 0 0 0 0 0 0 2 3 0 0
1 1 0 0 0 0 0 0 0 0 0 6 0 0
2 0 0 0 0 0 0 0 0 1 0 4 0 0
2 1 0 0 0 0 0 0 0 0 1 3 0 0且数据表中只有3个字段
studid int型
type bit型
publishTime smalldatetime型
,[1月]=sum(case when month(publishTime)=1 then count(*) else 0 end)
,[2月]=sum(case when month(publishTime)=2 then count(*) else 0 end)
,[3月]=sum(case when month(publishTime)=3 then count(*)else 0 end)
,[4月]=sum(case when month(publishTime)=2 then count(*) else 0 end)
,[5月]=sum(case when month(publishTime)=2 then count(*) else 0 end)
,[6月]=sum(case when month(publishTime)=2 then count(*) else 0 end)
,[7月]=sum(case when month(publishTime)=2 then count(*) else 0 end)
,[8月]=sum(case when month(publishTime)=8 then count(*)else 0 end)
,[9月]=sum(case when month(publishTime)=9 then count(*)else 0 end)
,[10月]=sum(case when month(publishTime)=10 then count(*)else 0 end)
,[11月]=sum(case when month(publishTime)=2 then count(*) else 0 end)
,[12月]=sum(case when month(publishTime)=2 then count(*) else 0 end)from AnsProc where year(publishTime)=2008 group by studid,type
SELECT studid, type
,[1月]=sum(case when month(publishTime)=1 then count(*) else 0 end)
,[2月]=sum(case when month(publishTime)=2 then count(*) else 0 end)
,[3月]=sum(case when month(publishTime)=3 then count(*)else 0 end)
,[4月]=sum(case when month(publishTime)=4 then count(*) else 0 end)
,[5月]=sum(case when month(publishTime)=5 then count(*) else 0 end)
,[6月]=sum(case when month(publishTime)=6 then count(*) else 0 end)
,[7月]=sum(case when month(publishTime)=7 then count(*) else 0 end)
,[8月]=sum(case when month(publishTime)=8 then count(*)else 0 end)
,[9月]=sum(case when month(publishTime)=9 then count(*)else 0 end)
,[10月]=sum(case when month(publishTime)=10 then count(*)else 0 end)
,[11月]=sum(case when month(publishTime)=11 then count(*) else 0 end)
,[12月]=sum(case when month(publishTime)=12 then count(*) else 0 end)
from AnsProc where year(publishTime)=2008
group by studid,type
不能对包含聚合或子查询的表达式执行聚合函数。
SELECT studid, type
,[1月]=count(case when month(publishTime)=1 then type end)
,[2月]=count(case when month(publishTime)=2 then type end)
,[3月]=count(case when month(publishTime)=3 then type end)
,[4月]=count(case when month(publishTime)=4 then type end)
,[5月]=count(case when month(publishTime)=5 then type end)
,[6月]=count(case when month(publishTime)=6 then type end)
,[7月]=count(case when month(publishTime)=7 then type end)
,[8月]=count(case when month(publishTime)=8 then type end)
,[9月]=count(case when month(publishTime)=9 then type end)
,[10月]=count(case when month(publishTime)=10 then type end)
,[11月]=count(case when month(publishTime)=11 then type end)
,[12月]=count(case when month(publishTime)=12 then type end)
from AnsProc where year(publishTime)=2008
group by type,studid