学生实习情况信息
主键 开始日期 结束日期 实习地点 学生id
id begin_date end_date workplace student_id
1 2010-02-02 2010-04-02 131001 3
2 2010-04-02 2010-05-02 131003 3
3 2010-01-02 2010-02-02 131001 1
4 2010-02-02 2010-03-11 131004 1
5 2010-02-04 2010-03-22 131006 5
6 2010-03-02 2010-05-02 131006 7上表是个学生实习情况信息表,每个学生可能有多条实习信息,以学生id来判断是否为同一个学生的实习信息,以每个学生开始日期最早的那条实习信息为他的初次实习信息,实习地点有6个,都是字典值,从131001至131006,我要统计的是每个实习地点,学生初次实习的人数,是“初次实习”才统计在内,没有学生初次实习的实习地点人数为0,像上表的统计结果就应该是实习地点 人数
131001 2
131002 0
131003 0
131004 0
131005 0
131006 2想了一下午头都大了,觉得这个SQL语句很复杂啊,主要是不知道怎么过滤出每个学生的初次实习信息,过滤出来以后就好办了,直接sum(case when workplace=131XXX then then 1 else 0 end) from 过滤出来的子表
主键 开始日期 结束日期 实习地点 学生id
id begin_date end_date workplace student_id
1 2010-02-02 2010-04-02 131001 3
2 2010-04-02 2010-05-02 131003 3
3 2010-01-02 2010-02-02 131001 1
4 2010-02-02 2010-03-11 131004 1
5 2010-02-04 2010-03-22 131006 5
6 2010-03-02 2010-05-02 131006 7上表是个学生实习情况信息表,每个学生可能有多条实习信息,以学生id来判断是否为同一个学生的实习信息,以每个学生开始日期最早的那条实习信息为他的初次实习信息,实习地点有6个,都是字典值,从131001至131006,我要统计的是每个实习地点,学生初次实习的人数,是“初次实习”才统计在内,没有学生初次实习的实习地点人数为0,像上表的统计结果就应该是实习地点 人数
131001 2
131002 0
131003 0
131004 0
131005 0
131006 2想了一下午头都大了,觉得这个SQL语句很复杂啊,主要是不知道怎么过滤出每个学生的初次实习信息,过滤出来以后就好办了,直接sum(case when workplace=131XXX then then 1 else 0 end) from 过滤出来的子表
go
create table [tb]([id] int,[begin_date] datetime,[end_date] datetime,[workplace] int,[student_id] int)
insert [tb]
select 1,'2010-02-02','2010-04-02',131001,3 union all
select 2,'2010-04-02','2010-05-02',131003,3 union all
select 3,'2010-01-02','2010-02-02',131001,1 union all
select 4,'2010-02-02','2010-03-11',131004,1 union all
select 5,'2010-02-04','2010-03-22',131006,5 union all
select 6,'2010-03-02','2010-05-02',131006,7
goselect a.workplace as 实习地点,isnull(b.cnt,0) as 人数
from(
select 131001 as workplace
union all select 131002
union all select 131003
union all select 131004
union all select 131005
union all select 131006
) a
left join
(select workplace,count(1) as cnt
from tb t
where not exists(select 1 from tb where student_id=t.student_id and id<t.id)
group by workplace
) b
on a.workplace=b.workplace
/**
实习地点 人数
----------- -----------
131001 2
131002 0
131003 0
131004 0
131005 0
131006 2(6 行受影响)
**/
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (id int,begin_date datetime,end_date datetime,workplace int,student_id int)
insert into [tb]
select 1,'2010-02-02','2010-04-02',131001,3 union all
select 2,'2010-04-02','2010-05-02',131003,3 union all
select 3,'2010-01-02','2010-02-02',131001,1 union all
select 4,'2010-02-02','2010-03-11',131004,1 union all
select 5,'2010-02-04','2010-03-22',131006,5 union all
select 6,'2010-03-02','2010-05-02',131006,7select a.workplace,人数=count(b.id) from
(select workplace='131001' union all select '131002' union all select '131003' union all select '131004' union all select '131005' union all select '131006') a
left join
(select * from tb t where not exists(select 1 from tb where student_id=t.student_id and begin_date<t.begin_date))b
on a.workplace=b.workplace
group by a.workplace
--结果:
workplace 人数
--------- -----------
131001 2
131002 0
131003 0
131004 0
131005 0
131006 2
select * from tb t
where not exists(select 1 from tb where student_id=t.student_id and begin_date<t.begin_date)
/*
id begin_date end_date workplace student_id
----------- ------------------------------------------------------ ------------------------------------------------------ ----------- -----------
1 2010-02-02 00:00:00.000 2010-04-02 00:00:00.000 131001 3
3 2010-01-02 00:00:00.000 2010-02-02 00:00:00.000 131001 1
5 2010-02-04 00:00:00.000 2010-03-22 00:00:00.000 131006 5
6 2010-03-02 00:00:00.000 2010-05-02 00:00:00.000 131006 7(所影响的行数为 4 行)
*/