有2个表
t1 t2
id name id glid year
1 t 1 1 2001
2 u 2 1 2002
3 v 3 2 2001
4 2 2002
5 3 2002
6 3 2003t1、t2用id和glid进行关联
假设t1是人员表,t2是培训表。我要统计每年培训的人数,但是有点特殊就是只统计最小年度
比如人员t参加了2次培训,2000和2001,只统计2000年的纪录,u也是一样只统计2001年的纪录依此类推。
最后结果应该是
年度 人数
2001 2
2002 1
t1 t2
id name id glid year
1 t 1 1 2001
2 u 2 1 2002
3 v 3 2 2001
4 2 2002
5 3 2002
6 3 2003t1、t2用id和glid进行关联
假设t1是人员表,t2是培训表。我要统计每年培训的人数,但是有点特殊就是只统计最小年度
比如人员t参加了2次培训,2000和2001,只统计2000年的纪录,u也是一样只统计2001年的纪录依此类推。
最后结果应该是
年度 人数
2001 2
2002 1
declare @t table(id int,name varchar(10))
insert into @t select 1,'t'
union all select 2,'u'
union all select 3,'v'
union all select 4,'d' declare @a table(id int,glid int,[year] varchar(10))
insert into @a select 1,1,'2001'
union all select 2,1,'2002'
union all select 3,2,'2001'
union all select 4,2,'2002'
union all select 5,3,'2002'
union all select 6,3,'2003'
union all select 7,4,'2006'
union all select 8,4,'2003'select [year],
[count]=count(*)
from @a a where not exists(select 1 from @a where glid=a.glid and [year]<a.[year])
group by [year]
insert into @t1 select 1,'t'
insert into @t1 select 2,'u'
insert into @t1 select 3,'v'
declare @t2 table(id int,glid int,year int)
insert into @t2 select 1,1,2001
insert into @t2 select 2,1,2002
insert into @t2 select 3,2,2001
insert into @t2 select 4,2,2002
insert into @t2 select 5,3,2002
insert into @t2 select 6,3,2003
select
年度 = b.year,
人数 = count(a.id)
from
@t1 a,(select glid,min(year) as year from @t2 group by glid) b
where
a.id=b.glid
group by
b.year/*
年度 人数
----------- -----------
2001 2
2002 1
*/
insert into @t1 select 1,'t'
insert into @t1 select 2,'u'
insert into @t1 select 3,'v'declare @t2 table(id int,glid int,year int)
insert into @t2 select 1,1,2001
insert into @t2 select 2,1,2002
insert into @t2 select 3,2,2001
insert into @t2 select 4,2,2002
insert into @t2 select 5,3,2002
insert into @t2 select 6,3,2003
select
年度 = b.year,
人数 = count(a.id)
from
@t1 a,@t2 b
where
a.id=b.glid
and
not exists(select 1 from @t2 where glid=b.glid and year<b.year)
group by
b.year/*
年度 人数
----------- -----------
2001 2
2002 1
*/
count(*) coun
from t2 a
where a.year = (select min(year) from t2 where glid = a.glid)
group by a.year