select id=identity(int,1,1),* into # from [Table]select 姓名,流程=(select 流程 from # where id=a.id-1) from # a where 流程='上班'
--> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] create table [tb]([姓名] varchar(6),[流程] varchar(6)) go insert [tb] select '李连杰','起床' union all select '李连杰','洗脸' union all select '李连杰','吃早饭' union all select '李连杰','上班' union all select '胡锦涛','起床' union all select '胡锦涛','洗脸' union all select '胡锦涛','上班'select id=identity(int,1,1),* into #temp from [tb]select [姓名],[流程] from #temp t where id = (select id - 1 from #temp where [姓名]=t.[姓名] and [流程]='上班')drop table #temp -------------------------- 李连杰 吃早饭 胡锦涛 洗脸
--> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] create table [tb]([姓名] varchar(6),[流程] varchar(6)) go insert [tb] select '李连杰','起床' union all select '李连杰','洗脸' union all select '李连杰','吃早饭' union all select '李连杰','上班' union all select '胡锦涛','起床' union all select '胡锦涛','洗脸' union all select '胡锦涛','上班'select id=identity(int,1,1),* into #temp from [tb]select 姓名,流程=(select 流程 from #temp where id=a.id-1) from #temp a where 流程='上班'drop table #temp -------------------------- 李连杰 吃早饭 胡锦涛 洗脸
你这个需要个序号字段,针对每个姓名有唯一的序号才行. create table [tb](id int ,[姓名] varchar(6),[流程] varchar(6)) go insert [tb] select 1, '李连杰','起床' union all select 2,'李连杰','洗脸' union all select 3,'李连杰','吃早饭' union all select 4,'李连杰','上班' union all select 5,'胡锦涛','起床' union all select 6,'胡锦涛','洗脸' union all select 7,'胡锦涛','上班' goselect t.* from tb t where id in (select max(id) from tb m where 姓名 = t.姓名 and id < (select id from tb where 姓名 = m.姓名 and 流程 = '上班')) drop table tb/* id 姓名 流程 ----------- ------ ------ 3 李连杰 吃早饭 6 胡锦涛 洗脸(所影响的行数为 2 行) */否则采用上面的临时表的方法.
from # a where 流程='上班'
if object_id('[tb]') is not null drop table [tb]
create table [tb]([姓名] varchar(6),[流程] varchar(6))
go
insert [tb]
select '李连杰','起床' union all
select '李连杰','洗脸' union all
select '李连杰','吃早饭' union all
select '李连杰','上班' union all
select '胡锦涛','起床' union all
select '胡锦涛','洗脸' union all
select '胡锦涛','上班'select id=identity(int,1,1),* into #temp from [tb]select [姓名],[流程]
from #temp t
where id = (select id - 1 from #temp where [姓名]=t.[姓名] and [流程]='上班')drop table #temp
--------------------------
李连杰 吃早饭
胡锦涛 洗脸
if object_id('[tb]') is not null drop table [tb]
create table [tb]([姓名] varchar(6),[流程] varchar(6))
go
insert [tb]
select '李连杰','起床' union all
select '李连杰','洗脸' union all
select '李连杰','吃早饭' union all
select '李连杰','上班' union all
select '胡锦涛','起床' union all
select '胡锦涛','洗脸' union all
select '胡锦涛','上班'select id=identity(int,1,1),* into #temp from [tb]select 姓名,流程=(select 流程 from #temp where id=a.id-1)
from #temp a where 流程='上班'drop table #temp
--------------------------
李连杰 吃早饭
胡锦涛 洗脸
create table [tb](id int ,[姓名] varchar(6),[流程] varchar(6))
go
insert [tb]
select 1, '李连杰','起床' union all
select 2,'李连杰','洗脸' union all
select 3,'李连杰','吃早饭' union all
select 4,'李连杰','上班' union all
select 5,'胡锦涛','起床' union all
select 6,'胡锦涛','洗脸' union all
select 7,'胡锦涛','上班'
goselect t.* from tb t where id in (select max(id) from tb m where 姓名 = t.姓名 and id < (select id from tb where 姓名 = m.姓名 and 流程 = '上班')) drop table tb/*
id 姓名 流程
----------- ------ ------
3 李连杰 吃早饭
6 胡锦涛 洗脸(所影响的行数为 2 行)
*/否则采用上面的临时表的方法.