谢谢各位帮忙:
想要查找每一个职员,前三回所在的部门编号。注:部门编号每变一次为一回。
查询表
支给年月 职员编号 部门编号
200910 001 101
200911 002 102
200912 003 103
200909 001 101
200908 001 201
200907 002 202
200906 003 203
200801 001 301
200802 002 102
200803 003 303
200701 001 401
200702 002 402
200703 003 403
想要结果:
支给年月 职员编号 部门编号
200910 001 101
200908 001 201
200801 001 301
200911 002 102
200907 002 202
200802 002 102
200912 003 103
200906 003 203
200803 003 303
想要查找每一个职员,前三回所在的部门编号。注:部门编号每变一次为一回。
查询表
支给年月 职员编号 部门编号
200910 001 101
200911 002 102
200912 003 103
200909 001 101
200908 001 201
200907 002 202
200906 003 203
200801 001 301
200802 002 102
200803 003 303
200701 001 401
200702 002 402
200703 003 403
想要结果:
支给年月 职员编号 部门编号
200910 001 101
200908 001 201
200801 001 301
200911 002 102
200907 002 202
200802 002 102
200912 003 103
200906 003 203
200803 003 303
declare @t table (支给年月 int,职员编号 varchar(3),部门编号 int)
insert into @t
select 200910,'001',101 union all
select 200911,'002',102 union all
select 200912,'003',103 union all
select 200909,'001',101 union all
select 200908,'001',201 union all
select 200907,'002',202 union all
select 200906,'003',203 union all
select 200801,'001',301 union all
select 200802,'002',102 union all
select 200803,'003',303 union all
select 200701,'001',401 union all
select 200702,'002',402 union all
select 200703,'003',403;with szy as(
select * from @t t
where not exists(select * from @t where 职员编号=t.职员编号 and 部门编号=t.部门编号 and 支给年月<t.支给年月)
)
select * from szy t
where (select count(*) from szy where 职员编号=t.职员编号 and 支给年月<t.支给年月)<3
order by 职员编号,支给年月,部门编号支给年月 职员编号 部门编号
----------- ---- -----------
200701 001 401
200801 001 301
200908 001 201
200702 002 402
200802 002 102
200907 002 202
200703 003 403
200803 003 303
200906 003 203(9 行受影响)
;with szy as(
select * from @t t
where not exists(select * from @t where 职员编号=t.职员编号 and 部门编号=t.部门编号 and 支给年月<t.支给年月)
)
select * from szy t
where (select count(*) from szy where 职员编号=t.职员编号 and 支给年月<t.支给年月)<3
order by 职员编号,支给年月,部门编号
-----> sql 2000select * into #t
from @t t
where not exists(select * from @t where 职员编号=t.职员编号 and 部门编号=t.部门编号 and 支给年月<t.支给年月)select * from #t t
where (select count(*) from #t where 职员编号=t.职员编号 and 支给年月<t.支给年月)<3
order by 职员编号,支给年月,部门编号drop table #t
select * from @t t
where (select count(*) from @t where 职员编号=t.职员编号 and 支给年月<t.支给年月)<3
and not exists(select * from @t where 职员编号=t.职员编号 and 部门编号=t.部门编号 and 支给年月<t.支给年月)
order by 职员编号,支给年月,部门编号
insert into tb values('200910', '001', '101')
insert into tb values('200911', '002', '102')
insert into tb values('200912', '003', '103')
insert into tb values('200909', '001', '101')
insert into tb values('200908', '001', '201')
insert into tb values('200907', '002', '202')
insert into tb values('200906', '003', '203')
insert into tb values('200801', '001', '301')
insert into tb values('200802', '002', '102')
insert into tb values('200803', '003', '303')
insert into tb values('200701', '001', '401')
insert into tb values('200702', '002', '402')
insert into tb values('200703', '003', '403')
goselect m.支给年月 , m.职员编号 ,m.部门编号 from
(
select t.* , px = (select count(1) from tb where 职员编号 = t.职员编号 and 部门编号 = t.部门编号 and 支给年月 > t.支给年月) + 1 from tb t
) m where px = 1 and 支给年月 in (select top 3 支给年月 from
(
select t.* , px = (select count(1) from tb where 职员编号 = t.职员编号 and 部门编号 = t.部门编号 and 支给年月 > t.支给年月) + 1 from tb t
) n where px = 1 and 职员编号 = m.职员编号 order by 支给年月 desc)
order by m.职员编号 , m.支给年月 descdrop table tb /*
支给年月 职员编号 部门编号
---------- ---------- ----------
200910 001 101
200908 001 201
200801 001 301
200911 002 102
200907 002 202
200702 002 402
200912 003 103
200906 003 203
200803 003 303(所影响的行数为 9 行)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-03 23:41:59
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([支给年月] int,[职员编号] varchar(3),[部门编号] int)
insert [tb]
select 200910,'001',101 union all
select 200911,'002',102 union all
select 200912,'003',103 union all
select 200909,'001',101 union all
select 200908,'001',201 union all
select 200907,'002',202 union all
select 200906,'003',203 union all
select 200801,'001',301 union all
select 200802,'002',102 union all
select 200803,'003',303 union all
select 200701,'001',401 union all
select 200702,'002',402 union all
select 200703,'003',403
--------------开始查询--------------------------
;with f as
(
select id=row_number()over(partition by 职员编号,部门编号 order by 部门编号),* from [tb]
)
select 支给年月,职员编号,部门编号
from
(
SELECT * FROM f AS T WHERE 3>(SELECT COUNT(*) FROM f WHERE 职员编号=T.职员编号 AND 支给年月>T.支给年月 and id=1)
)t
where id=1
-------------结果----------------------------
/* 支给年月 职员编号 部门编号
----------- ---- -----------
200909 001 101
200908 001 201
200801 001 301
200802 002 102
200907 002 202
200702 002 402
200912 003 103
200906 003 203
200803 003 303(9 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-03 23:41:59
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([支给年月] int,[职员编号] varchar(3),[部门编号] int)
insert [tb]
select 200910,'001',101 union all
select 200911,'002',102 union all
select 200912,'003',103 union all
select 200909,'001',101 union all
select 200908,'001',201 union all
select 200907,'002',202 union all
select 200906,'003',203 union all
select 200801,'001',301 union all
select 200802,'002',102 union all
select 200803,'003',303 union all
select 200701,'001',401 union all
select 200702,'002',402 union all
select 200703,'003',403
--------------开始查询--------------------------
;with f as
(
select id=row_number()over(partition by 职员编号,部门编号 order by 部门编号),* from [tb]
)
select 支给年月,职员编号,部门编号
from
(
SELECT * FROM f AS T WHERE 3>(SELECT COUNT(*) FROM f WHERE 职员编号=T.职员编号 AND 支给年月<T.支给年月 and id=1)
)t
where id=1
-------------结果----------------------------
/* 支给年月 职员编号 部门编号
----------- ---- -----------
200908 001 201
200801 001 301
200701 001 401
200802 002 102
200907 002 202
200702 002 402
200906 003 203
200803 003 303
200703 003 403(9 行受影响)
*/
select * from @t t
where (select count(*) from @t where 职员编号=t.职员编号 and 支给年月<t.支给年月)<3
--and not exists(select * from @t where 职员编号=t.职员编号 and 部门编号=t.部门编号 and 支给年月<t.支给年月)
order by 职员编号,支给年月,部门编号
支给年月 职员编号 部门编号
----------- ---- -----------
200701 001 401
200801 001 301
200908 001 201
200702 002 402
200802 002 102
200907 002 202
200703 003 403
200803 003 303
200906 003 203(9 行受影响)
create table tb(支给年月 varchar(10) , 职员编号 varchar(10),部门编号 varchar(10))
insert into tb values('200910', '001', '101')
insert into tb values('200911', '002', '102')
insert into tb values('200912', '003', '103')
insert into tb values('200909', '001', '101')
insert into tb values('200908', '001', '201')
insert into tb values('200907', '002', '202')
insert into tb values('200906', '003', '203')
insert into tb values('200801', '001', '301')
insert into tb values('200802', '002', '102')
insert into tb values('200803', '003', '303')
insert into tb values('200701', '001', '401')
insert into tb values('200702', '002', '402')
insert into tb values('200703', '003', '403')
create table tb2(支给年月 varchar(10) , 职员编号 varchar(10),部门编号 varchar(10))
godeclare @支给年月 varchar(10) , @职员编号 varchar(10),@部门编号 varchar(10)
declare @支给年月2 varchar(10) , @职员编号2 varchar(10),@部门编号2 varchar(10)
set @支给年月2 = ''
set @职员编号2 = ''
set @部门编号2 = ''
declare @i as int
set @i = 0
declare cur cursor fast_forward for
select * from tb order by 职员编号 , 支给年月 desc;
open cur;
fetch next from cur into @支给年月 , @职员编号 ,@部门编号;
while @@fetch_status=0
begin
if @职员编号 <> @职员编号2
begin
set @职员编号2 = @职员编号
set @部门编号2 = @部门编号
insert into tb2 select @支给年月 , @职员编号 ,@部门编号
set @i = 1
end
if @i < 3
begin
if @部门编号 <> @部门编号2
begin
set @部门编号2 = @部门编号
insert into tb2 select @支给年月 , @职员编号 ,@部门编号
set @i = @i + 1
end
end
fetch next from cur into @支给年月 , @职员编号 ,@部门编号;
end
close cur;
deallocate cur;select * from tb2 order by 职员编号 , 支给年月 descdrop table tb ,tb2/*
支给年月 职员编号 部门编号
---------- ---------- ----------
200910 001 101
200908 001 201
200801 001 301
200911 002 102
200907 002 202
200802 002 102
200912 003 103
200906 003 203
200803 003 303(所影响的行数为 9 行)
*/
想要查找每一个职员,前三回所在的部门编号。注:部门编号每变一次为一回。 改为:
想要查找每一个职员,最近(日期)三次所在的部门编号。注:部门编号每变一次为一回。
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([支给年月] int,[职员编号] varchar(3),[部门编号] int)
insert [TB]
select 200910,'001',101 union all
select 200911,'002',102 union all
select 200912,'003',103 union all
select 200909,'001',101 union all
select 200908,'001',201 union all
select 200907,'002',202 union all
select 200906,'003',203 union all
select 200801,'001',301 union all
select 200802,'002',102 union all
select 200803,'003',303 union all
select 200701,'001',401 union all
select 200702,'002',402 union all
select 200703,'003',403select * from TB A where 支给年月 in(
select top 3 支给年月 from TB T where A.职员编号=职员编号 and not exists
(select 1 from TB where 职员编号=T.职员编号 and 部门编号=T.部门编号 and 支给年月>t.支给年月))
order by 职员编号/*
支给年月 职员编号 部门编号
----------- ---- -----------
200910 001 101
200908 001 201
200801 001 301200907 002 202
200911 002 102
200702 002 402200912 003 103
200906 003 203
200803 003 303(所影响的行数为 9 行)
*/drop table TB
修改了一下下,在看看
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([支给年月] int,[职员编号] varchar(3),[部门编号] int)
insert [TB]
select 200910,'001',101 union all
select 200911,'002',102 union all
select 200912,'003',103 union all
select 200909,'001',101 union all
select 200908,'001',201 union all
select 200907,'002',202 union all
select 200906,'003',203 union all
select 200801,'001',301 union all
select 200802,'002',102 union all
select 200803,'003',303 union all
select 200701,'001',401 union all
select 200702,'002',402 union all
select 200703,'003',403select * from TB A where 支给年月 in(
select top 3 支给年月 from TB T where A.职员编号=职员编号 and not exists
(select 1 from TB where 职员编号=T.职员编号 and 部门编号=T.部门编号 and 支给年月<t.支给年月))
order by 职员编号/*
支给年月 职员编号 部门编号
----------- ---- -----------
200909 001 101
200908 001 201
200801 001 301
200802 002 102
200907 002 202
200702 002 402
200912 003 103
200803 003 303
200906 003 203(所影响的行数为 9 行)*/
/********
SQL VERSION:SQL2005 standard SP3
脚本日期: 2009-12-04 12:48:01.510 ******/create table SelTable(支给年月 nvarchar(10),职员编号 nvarchar(10),部门编号 nvarchar(10))
insert into SelTable
select '200910','001','101' union all
select '200911','002','102' union all
select '200912','003','103' union all
select '200909','001','101' union all
select '200908','001','201' union all
select '200907','002','202' union all
select '200906','003','203' union all
select '200801','001','301' union all
select '200802','002','102' union all
select '200803','003','303' union all
select '200701','001','401' union all
select '200702','002','402' union all
select '200703','003','403'
--开始测试exec Test_Proc--结束测试--输出结果
支给年月 职员编号 部门编号
200909 001 101
200908 001 201
200801 001 301
200911 002 102
200907 002 202
200802 002 102
200912 003 103
200906 003 203
200803 003 303/*********Test_Proc 存储过程************/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOalter PROCEDURE Test_Procas
declare @WokerNum nvarchar(200)--职员编号
declare @Time nvarchar(200)--支给年月
declare @m int
declare @n int
declare @id nvarchar(20)
set @m=0
set @n=0
set @id=0
set @WokerNum=''
set @Time=''
begin
create table #aa(编号 int,支给年月 nvarchar(10),职员编号 nvarchar(10),部门编号 nvarchar(10))
insert into #aa
select
row_number() over(order by 职员编号,支给年月 desc)编号,
*
from
selTable
order by
职员编号,支给年月
desc
create table #bb(编号 int,支给年月 nvarchar(10),职员编号 nvarchar(10),部门编号 nvarchar(10))
insert into #bb
select * from #aa as a where 编号 in
(
case when (
select
部门编号
from
#aa
where
编号=(a.编号+1))=a.部门编号
then
a.编号+1 else a.编号
end
)
drop table #aa--
select @WokerNum=@WokerNum+职员编号+',' from #bb group by 职员编号 set @m=CHARINDEX(',',@WokerNum)
set @n=1
while @m>0
begin
set @id=substring(@WokerNum,@n,@m-@n)
begin
select @Time=@Time+''''+convert(nvarchar(10),支给年月)+''''+',' from #bb where 编号 in (select top 3 编号 from #bb where 职员编号=@id)
end
set @n=@m+1
set @m=CHARINDEX(',',@WokerNum,@n)
end set @Time=substring(@Time,1,len(@Time)-1) drop table #bb--
exec('select * from SelTable where 支给年月 in ('+@Time+') order by 职员编号,支给年月 desc')
end
GO