--> 测试数据: [ss] if object_id('[ss]') is not null drop table [ss] create table [ss] (HDID int,timstamp datetime,lname varchar(4),type varchar(5)) insert into [ss] select 11,'2008-7-1 10:10','张三','Fpass' union all select 11,'2008-7-1 10:20','王五','SSH' union all select 12,'2008-7-2 12:10','张三','Fpass' union all select 12,'2008-7-3 09:20','王五','SSH' union all select 12,'2008-7-3 16:00','张三','Fpass' union all select 12,'2008-7-3 18:30','李四','KGMR' union all select 13,'2008-6-30 12:00','张三','Fpass' union all select 14,'2008-8-30 19:00','李四','SSH' union all select 15,'2007-1-5 16:00','李四','KGMR'--查询 select ypx=case type when 'Fpass' then 1 else 2 end,* into # from [ss] aselect hdid, begintime=max(case ypx when 1 then timstamp else null end), endtime=max(case ypx when 2 then timstamp else null end), lname=max(case ypx when 2 then lname else null end), newtype=isnull(max(case ypx when 2 then type else null end),'null')+'---'+isnull(max(case ypx when 1 then type else null end),'null') from (select px=(select count(1) from # where hdid=a.hdid and ypx=a.ypx and timstamp<=a.timstamp),* from # a)a group by hdid,px order by hdid --结果: hdid begintime endtime lname newtype ----------- ------------------------------------------------------ ------------------------------------------------------ ----- ------------- 11 2008-07-01 10:10:00.000 2008-07-01 10:20:00.000 王五 SSH---Fpass 12 2008-07-02 12:10:00.000 2008-07-03 09:20:00.000 王五 SSH---Fpass 12 2008-07-03 16:00:00.000 2008-07-03 18:30:00.000 李四 KGMR---Fpass 13 2008-06-30 12:00:00.000 NULL NULL null---Fpass 14 NULL 2008-08-30 19:00:00.000 李四 SSH---null 15 NULL 2007-01-05 16:00:00.000 李四 KGMR---null(所影响的行数为 6 行)
--> 测试数据: [ss]
if object_id('[ss]') is not null drop table [ss]
create table [ss] (HDID int,timstamp datetime,lname varchar(4),type varchar(5))
insert into [ss]
select 11,'2008-7-1 10:10','张三','Fpass' union all
select 11,'2008-7-1 10:20','王五','SSH' union all
select 12,'2008-7-2 12:10','张三','Fpass' union all
select 12,'2008-7-3 09:20','王五','SSH' union all
select 12,'2008-7-3 16:00','张三','Fpass' union all
select 12,'2008-7-3 18:30','李四','KGMR' union all
select 13,'2008-6-30 12:00','张三','Fpass' union all
select 14,'2008-8-30 19:00','李四','SSH' union all
select 15,'2007-1-5 16:00','李四','KGMR'--查询
select ypx=case type when 'Fpass' then 1 else 2 end,* into # from [ss] aselect hdid,
begintime=max(case ypx when 1 then timstamp else null end),
endtime=max(case ypx when 2 then timstamp else null end),
lname=max(case ypx when 2 then lname else null end),
newtype=isnull(max(case ypx when 2 then type else null end),'null')+'---'+isnull(max(case ypx when 1 then type else null end),'null')
from (select px=(select count(1) from # where hdid=a.hdid and ypx=a.ypx and timstamp<=a.timstamp),* from # a)a
group by hdid,px
order by hdid
--结果:
hdid begintime endtime lname newtype
----------- ------------------------------------------------------ ------------------------------------------------------ ----- -------------
11 2008-07-01 10:10:00.000 2008-07-01 10:20:00.000 王五 SSH---Fpass
12 2008-07-02 12:10:00.000 2008-07-03 09:20:00.000 王五 SSH---Fpass
12 2008-07-03 16:00:00.000 2008-07-03 18:30:00.000 李四 KGMR---Fpass
13 2008-06-30 12:00:00.000 NULL NULL null---Fpass
14 NULL 2008-08-30 19:00:00.000 李四 SSH---null
15 NULL 2007-01-05 16:00:00.000 李四 KGMR---null(所影响的行数为 6 行)
HDID Timestamp TYPE LNAME
90045711 2008-09-01 15:05:31 CHOP 管理岗
90045711 2008-09-01 15:16:30 CHAP1 审批岗1
90045711 2008-09-01 15:32:28 CHAP1 审批岗2
90045711 2008-09-01 15:46:45 CHAP1 审批岗3
90045711 2008-09-01 15:47:32 CHAP1 审批岗4
90045711 2008-09-01 16:40:41 CHAP1 审批岗5
90045711 2008-09-01 16:47:57 CHAP1 审批岗6
那么新表要求:
时间最先的审批岗与管理岗作为新的一行记录。之后的审批岗分别与时间最先的审批岗作为新的一行记录。也就是如下:
HDID BeginTime ENDTIME NewNANE
90045711 2008-09-01 15:05:31 2008-09-01 15:16:30 审批岗1-管理岗
90045711 2008-09-01 15:16:30 2008-09-01 15:32:28 审批岗2-审批岗1
90045711 2008-09-01 15:16:30 2008-09-01 15:46:45 审批岗3-审批岗1
90045711 2008-09-01 15:16:30 2008-09-01 15:47:32 审批岗4-审批岗1
90045711 2008-09-01 15:16:30 2008-09-01 16:40:41 审批岗5-审批岗1
90045711 2008-09-01 15:16:30 2008-09-01 16:47:57 审批岗6-审批岗1