支给年月 职员编号 部门编号
------ ---------- -----------
200901 0001 5001
200901 0002 5002
200901 0003 5003
200801 0001 4001
200801 0002 4002
200801 0003 4003
200701 0001 3001
200701 0002 3002
200701 0003 3003 想把以上结果转换成以下的形式,如何实现呢? 职员编号 去年部门编号 前年部门编号 大前年部门编号
0001 5001 4001 3001
0002 5002 4002 3002
0003 5003 4003 3003
------ ---------- -----------
200901 0001 5001
200901 0002 5002
200901 0003 5003
200801 0001 4001
200801 0002 4002
200801 0003 4003
200701 0001 3001
200701 0002 3002
200701 0003 3003 想把以上结果转换成以下的形式,如何实现呢? 职员编号 去年部门编号 前年部门编号 大前年部门编号
0001 5001 4001 3001
0002 5002 4002 3002
0003 5003 4003 3003
go
create table [tb]([支给年月] int,[职员编号] varchar(4),[部门编号] int)
insert [tb]
select 200901,'0001',5001 union all
select 200901,'0002',5002 union all
select 200901,'0003',5003 union all
select 200801,'0001',4001 union all
select 200801,'0002',4002 union all
select 200801,'0003',4003 union all
select 200701,'0001',3001 union all
select 200701,'0002',3002 union all
select 200701,'0003',3003 union all
select 200601,'0001',2001 union all
select 200601,'0002',2002 union all
select 200601,'0003',2003 union all
select 200501,'0001',1001 union all
select 200501,'0002',1002
select
职员编号,
max(case when left(支给年月,4)=year(getdate()) then 部门编号 else '' end) as [去年部门编号],
max(case when left(支给年月,4)=year(dateadd(yy,-1,getdate())) then 部门编号 else '' end) as [前年部门编号],
max(case when left(支给年月,4)=year(dateadd(yy,-2,getdate())) then 部门编号 else '' end) as [大前年部门编号]
from
tb
group by
职员编号/**
职员编号 去年部门编号 前年部门编号 大前年部门编号
---- ----------- ----------- -----------
0001 5001 4001 3001
0002 5002 4002 3002
0003 5003 4003 3003(所影响的行数为 3 行)
**/
insert into tb values('200901', '0001', 5001)
insert into tb values('200901', '0002', 5002)
insert into tb values('200901', '0003', 5003)
insert into tb values('200801', '0001', 4001)
insert into tb values('200801', '0002', 4002)
insert into tb values('200801', '0003', 4003)
insert into tb values('200701', '0001', 3001)
insert into tb values('200701', '0002', 3002)
insert into tb values('200701', '0003', 3003)
insert into tb values('200601', '0001', 2001)
insert into tb values('200601', '0002', 2002)
insert into tb values('200601', '0003', 2003)
insert into tb values('200501', '0001', 1001)
insert into tb values('200501', '0002', 1002)
goselect 职员编号 ,
max(case px when 1 then 部门编号 else 0 end) 去年部门编号,
max(case px when 2 then 部门编号 else 0 end) 前年部门编号,
max(case px when 3 then 部门编号 else 0 end) 大前年部门编号
from
(
select t.* , px = (select count(1) from tb where 职员编号 = t.职员编号 and 支给年月 > t.支给年月) + 1 from tb t
) m
group by 职员编号drop table tb/*
职员编号 去年部门编号 前年部门编号 大前年部门编号
---------- ----------- ----------- -----------
0001 5001 4001 3001
0002 5002 4002 3002
0003 5003 4003 3003(所影响的行数为 3 行)*/
go
create table [tb]([支给年月] int,[职员编号] varchar(4),[部门编号] int)
insert [tb]
select 200901,'0001',5001 union all
select 200901,'0002',5002 union all
select 200901,'0003',5003 union all
select 200801,'0001',4001 union all
select 200801,'0002',4002 union all
select 200801,'0003',4003 union all
select 200701,'0001',3001 union all
select 200701,'0002',3002 union all
select 200701,'0003',3003 union all
select 200601,'0001',2001 union all
select 200601,'0002',2002 union all
select 200601,'0003',2003 union all
select 200501,'0001',1001 union all
select 200501,'0002',1002
;with cte
as
(select * ,px=row_number()over(partition by [职员编号] order by [支给年月] desc) from tb)
select 职员编号 ,
max(case px when 1 then 部门编号 else 0 end) 去年部门编号,
max(case px when 2 then 部门编号 else 0 end) 前年部门编号,
max(case px when 3 then 部门编号 else 0 end) 大前年部门编号
from cte
group by 职员编号
/*
职员编号 去年部门编号 前年部门编号 大前年部门编号
---- ----------- ----------- -----------
0001 5001 4001 3001
0002 5002 4002 3002
0003 5003 4003 3003(3 個資料列受到影響)
*/
SELECT [职员编号],[200901],[200801],[200701] FROM
(SELECT [职员编号],支给年月,部门编号 FROM TB ) AS A
PIVOT
( MAX([部门编号])
FOR 支给年月 IN ([200901],[200801],[200701]) ) AS B
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-03 16:56:24
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([支给年月] int,[职员编号] varchar(4),[部门编号] int)
insert [tb]
select 200901,'0001',5001 union all
select 200901,'0002',5002 union all
select 200901,'0003',5003 union all
select 200801,'0001',4001 union all
select 200801,'0002',4002 union all
select 200801,'0003',4003 union all
select 200701,'0001',3001 union all
select 200701,'0002',3002 union all
select 200701,'0003',3003
--------------开始查询--------------------------
select
职员编号 ,
max(case px when 1 then 部门编号 else 0 end) 去年部门编号,
max(case px when 2 then 部门编号 else 0 end) 前年部门编号,
max(case px when 3 then 部门编号 else 0 end) 大前年部门编号
from
(select * ,px=row_number()over(partition by [职员编号] order by [支给年月] desc) from tb)t
group by
职员编号----------------结果----------------------------
/* 职员编号 去年部门编号 前年部门编号 大前年部门编号
---- ----------- ----------- -----------
0001 5001 4001 3001
0002 5002 4002 3002
0003 5003 4003 3003(3 行受影响)
*/