统计问题，

Question

--统计职称
表数据
ID,人员ID,聘任时间,聘任职称 
1,     1,     1998-2-1,初级工 
2,     1,     2000-2-1,中级工 
3,     1,     2005-3-1,高级工 
4,     2,     2002-2-1,初级工 
5,     2,     2004-5-4,中级工 如果@sj='2000-8-9',@sj2='2006-8-2'表示要统计2000年到2006年的职称聘任情况,因此要得到下面结果 
年份   ,   高级工,   中级工,   初级工 
2000   ,     0     ,       1,           0 
2001,       0,           1,           0 
2002,       0,           1,           1 
2003,       0             1,           1 
2004,       0,           2,           0 
2005,       1,           1,           0 
2006,       1,           1,           0  需要注意的是，同一个人如果升成高级或者中级后，只能算一次，比如人员ID为1的再2000年升成了中级，在2005年升成了高级，就只能算高级了。不能再算中级的了

Answer 1

--try:
--测试数据
create table po (id int,人员id int,聘任时间 datetime,聘任职称 varchar(20))
insert into po select 1,     1,     '1998-2-1','初级工' 
insert into po select 2,     1,     '2000-2-1','中级工' 
insert into po select 3,     1,     '2005-3-1','高级工' 
insert into po select 4,     2,     '2002-2-1','初级工' 
insert into po select 5,     2,     '2004-5-4','中级工' 
go--创建存储过程
create proc wsp
@sj datetime,
@sj1 datetime
as
declare @t table (sj varchar(10))
while(datepart(yy,@sj)<=datepart(yy,@sj1))
begin
insert into @t select datename(yy,@sj)
set @sj=dateadd(yy,1,@sj)
end select * into #temp from 
(select sj,a.* from @t left join po a
on datepart(yy,聘任时间)<=sj 
where  datepart(yy,聘任时间)<=datepart(yy,@sj1))b
select sj,
[高级工]=sum(case 聘任职称 when '高级工' then 1 else 0 end),
[中级工]=sum(case 聘任职称 when '中级工' then 1 else 0 end),
[初级工]=sum(case 聘任职称 when '初级工' then 1 else 0 end) 
from
(select * from #temp a
where 聘任时间=(select top 1 聘任时间 from #temp where 人员id=a.人员id and sj=a.sj order by 聘任时间 desc))a
group by sj
go--调用
exec wsp '2000-8-9','2006-8-2'
/*结果：
sj         高级工         中级工         初级工
---------- ----------- ----------- -----------
2000       0           1           0
2001       0           1           0
2002       0           1           1
2003       0           1           1
2004       0           2           0
2005       1           1           0
2006       1           1           0
*/

Answer 2

if object_id('tb') is not null
drop table tb 
go
create table tb(ID int,人员ID int,聘任时间 date ,聘任职称 varchar(10))
insert into tb
select 1, 1, '1998-2-1','初级工' union all  
select 2, 1, '2000-2-1','中级工' union all 
select 3, 1, '2005-3-1','高级工' union all   
select 4, 2, '2002-2-1','初级工' union all   
select 5, 2, '2004-5-4','中级工'  
declare @sj date,
        @sj2 date
set @sj='2000-8-9'
set @sj2='2006-8-2'
select YEAR(聘任时间) 年份, 高级工=sum(case 聘任职称 when '高级工' then 1 else 0 end),
                            中级工=sum(case 聘任职称 when '中级工' then 1 else 0 end),
                            初级工=sum(case 聘任职称 when '初级工' then 1 else 0 end)
from 
(
select 人员ID,聘任时间,聘任职称
from tb a
where exists ( select 1 from tb 
               where a.人员ID=人员ID 
               group by 人员ID
               having a.聘任时间=MAX(聘任时间))
) t
where 聘任时间 between @sj and @sj2
group by YEAR(聘任时间)年份 高级工 中级工 初级工
2004 0 1 0
2005 1 0 0是这个意思吗？

Answer 3

--或者稍微简化下：
--测试数据
create table po (id int,人员id int,聘任时间 datetime,聘任职称 varchar(20))
insert into po select 1,     1,     '1998-2-1','初级工' 
insert into po select 2,     1,     '2000-2-1','中级工' 
insert into po select 3,     1,     '2005-3-1','高级工' 
insert into po select 4,     2,     '2002-2-1','初级工' 
insert into po select 5,     2,     '2004-5-4','中级工' 
go--创建存储过程
create proc wsp
@sj datetime,
@sj1 datetime
as
select * into #temp from 
(select sj,a.* from (select sj=year(@sj)+number from master..spt_values where type='p' and number between 0 and datediff(yy,@sj,@sj1))b left join po a
on datepart(yy,聘任时间)<=b.sj 
where  datepart(yy,聘任时间)<=datepart(yy,@sj1))b
select sj,
[高级工]=sum(case 聘任职称 when '高级工' then 1 else 0 end),
[中级工]=sum(case 聘任职称 when '中级工' then 1 else 0 end),
[初级工]=sum(case 聘任职称 when '初级工' then 1 else 0 end) 
from
(select * from #temp a
where 聘任时间=(select top 1 聘任时间 from #temp where 人员id=a.人员id and sj=a.sj order by 聘任时间 desc))a
group by sj
go--调用
exec wsp '2000-8-9','2006-8-2'
/*结果：
sj         高级工         中级工         初级工
---------- ----------- ----------- -----------
2000       0           1           0
2001       0           1           0
2002       0           1           1
2003       0           1           1
2004       0           2           0
2005       1           1           0
2006       1           1           0
*/

Answer 4

-- 测试环境
if object_id('tb') is not null drop table tb
go
create table tb
(
id int,
人员ID int,
聘任时间 datetime,
聘任职称 varchar(20)
)
go
insert tb
select 1, 1, '1998-2-1','初级工' union all  
select 2, 1, '2000-2-1','中级工' union all  
select 3, 1, '2005-3-1','高级工' union all  
select 4, 2, '2002-2-1','初级工' union all  
select 5, 2, '2004-5-4','中级工' 
go-- 查询
declare @sj datetime,@sj2 datetime
select @sj='2000-8-9',@sj2='2006-8-2' 
select 年份=year(dateadd(year,number,@sj)),
高级工=sum(case 聘任职称 when '高级工' then 1 else 0 end),
中级工=sum(case 聘任职称 when '中级工' then 1 else 0 end),
初级工=sum(case 聘任职称 when '初级工' then 1 else 0 end)
from  master..spt_values left join tb on datediff(year,dateadd(year,number,@sj),聘任时间)=0
where type='p' and number between 0 and datediff(year,@sj,@sj2)
group by year(dateadd(year,number,@sj))
-- 结果
/*
年份          高级工         中级工         初级工
----------- ----------- ----------- -----------
2004        0           1           0
2001        0           0           0
2005        1           0           0
2002        0           0           1
2000        0           1           0
2003        0           0           0
2006        0           0           0(7 行受影响)*/     

Answer 5

谢谢各位，也谢谢jaydom和xys_777，虽然你们的结果并不是想要的。