农业学校 计算机 60 2009-01-01
农业学校 农学专业 30 2009-01-01
财政学校 会计 90 2009-02-01
财政学校 会计 88 2009-02-28
商业学校 贸易 22 2009-06-28
农业学校 计算机 66 2009-05-01
农业学校 农学专业 37 2009-05-05要求统计本市所有学校每月的在校生总数,及各专业学生的每月在校人数 ,用于生成折现图
注意学校并不是每个月都提供学生人数,没有当月统计数据的视为当月人数无变动
农业学校 农学专业 30 2009-01-01
财政学校 会计 90 2009-02-01
财政学校 会计 88 2009-02-28
商业学校 贸易 22 2009-06-28
农业学校 计算机 66 2009-05-01
农业学校 农学专业 37 2009-05-05要求统计本市所有学校每月的在校生总数,及各专业学生的每月在校人数 ,用于生成折现图
注意学校并不是每个月都提供学生人数,没有当月统计数据的视为当月人数无变动
select
a.*
from student a
where not exists(select 1 from student b on a.school = b.school and a.zhangye = b.zhangye and a.tjdate < b.tjdate)
农业学校 计算机 60 2009-01-01
农业学校 农学 30 2009-01-01
财政学校 会计 90 2009-02-01
财政学校 会计 88 2009-02-28
商业学校 贸易 22 2009-06-28
农业学校 计算机 66 2009-05-01
农业学校 农学 37 2009-05-05
农业学校 计算机 25 2009-06-01
商业学校 计算机 20 2009-06-01
想得到如下结果:月份 计算机 农学 会计 贸易 合计
1 60 30 0 0 90
2 60 30 88 0 178
3 60 30 88 0 178
4 60 30 88 0 178
5 66 37 88 0 201
6 45 30 88 22 185求sql
if OBJECT_ID('tab') is not null
drop table tab;
go
create table tab(school varchar(20),class varchar(20),num int,register date);
go
insert into tab values
('agr','computer',60,'2009-01-01'),('arg','agriculture',30,'2009-01-01'),
('fin','accounting',90,'2009-02-01'),('fin','accounting',88,'2009-02-28'),
('trd','trade',22,'2009-06-28'),('arg','computer',66,'2009-05-01'),
('arg','agriculture',37,'2009-05-05'),('arg','computer',25,'2009-06-01'),
('trd','computer',20,'2009-06-01');
goif OBJECT_ID('v_tab','v') is not null
drop view v_tab;
go
create view v_tab
as
with t1 as(
select class,num,month(register) [month]
from tab t where register=
(select MAX(register) from tab where t.school=school
and class=t.class and month(t.register)=MONTH(register))
),
t2 as(
select class,
ISNULL([1],0) [1],
ISNULL([2],ISNULL([1],0)) [2],
ISNULL([3],ISNULL([2],ISNULL([1],0))) [3],
ISNULL([4],ISNULL([3],ISNULL([2],ISNULL([1],0)))) [4],
ISNULL([5],ISNULL([4],ISNULL([3],ISNULL([2],ISNULL([1],0))))) [5],
ISNULL([6],ISNULL([5],ISNULL([4],ISNULL([3],ISNULL([2],ISNULL([1],0)))))) [6]
from t1 pivot (sum(num) for [month] in ([1],[2],[3],[4],[5],[6])) pvt
),
t3 as(
select * from t2 unpivot
(num for [month] in ([1],[2],[3],[4],[5],[6])) upvt
)
select [month],
SUM(case when [class]='accounting' then num else 0 end) [accounting],
SUM(case when [class]='agriculture' then num else 0 end) [agriculture],
SUM(case when [class]='computer' then num else 0 end) [computer],
SUM(case when [class]='trade' then num else 0 end) [trade],
SUM(num) [sum]
from t3
group by [month]
goselect * from v_tab;
/*
month accounting agriculture computer trade sum
1 0 30 60 0 90
2 88 30 60 0 178
3 88 30 60 0 178
4 88 30 60 0 178
5 88 37 66 0 191
6 88 37 45 22 192
*/
按照楼上提供的数据写的SQL
select month(register) montht,
SUM(case when [class]='accounting' then num else 0 end) [accounting],
SUM(case when [class]='agriculture' then num else 0 end) [agriculture],
SUM(case when [class]='computer' then num else 0 end) [computer],
SUM(case when [class]='trade' then num else 0 end) [trade],
SUM(num) [sum]
from tab
group by month(register)
-- SQL 2000
select * into #1 from
(select 'computer' class union select 'agriculture' union
select 'accounting' union select 'trade') t1
cross join
(select number from master.dbo.spt_values
where type='P' and number between 1 and 6) t2select t1.number [month],t1.class,t2.num into #2
from #1 t1 left join
(select class,num,month(register) [month]
from tab t where register=
(select MAX(register) from tab where t.school=school
and class=t.class and month(t.register)=MONTH(register))) t2
on t1.class=t2.class and t2.[month]=t1.numberselect [month],
sum(case when class='accounting' then num else 0 end) [accounting],
sum(case when class='agriculture' then num else 0 end) [agriculture],
sum(case when class='computer' then num else 0 end) [computer],
sum(case when class='trade' then num else 0 end) [trade],
sum(num) [sum]
from (
select [month],class,
isnull((select top 1 num from #2 where class=t.class and [month]<=t.[month]
and num is not null order by [month] desc),0) num
from #2 t) t
group by [month]
-- 这个应该够了
if object_id('v1','v') is not null
drop view v1;
go
create view v1 as
select t1.number [month],t1.class,sum(t2.num) num
from (select * from
(select 'computer' class union select 'agriculture' union
select 'accounting' union select 'trade') t1
cross join
(select number from master.dbo.spt_values
where type='P' and number between 1 and 6) t2) t1
left join
(select class,num,month(register) [month]
from tab t where register=
(select MAX(register) from tab where t.school=school
and class=t.class and month(t.register)=MONTH(register))) t2
on t1.class=t2.class and t2.[month]=t1.number
group by t1.number,t1.class
goif object_id('v_tab','v') is not null
drop table v_tab;
go
create view v_tab as
select [month],
sum(case when class='accounting' then num else 0 end) [accounting],
sum(case when class='agriculture' then num else 0 end) [agriculture],
sum(case when class='computer' then num else 0 end) [computer],
sum(case when class='trade' then num else 0 end) [trade],
sum(num) [sum]
from (
select [month],class,
isnull((select top 1 num from v1 where class=t.class and [month]<=t.[month]
and num is not null order by [month] desc),0) num
from v1 t) t
group by [month]select * from v_tab;
SQL 2000 的 query cost 99%
SQL 2005 的 query cost 1%