DECLARE @table_name VARCHAR(100) DECLARE @sql VARCHAR(1000) SET @table_name = '你的表名' SET @sql = 'SELECT * FROM ' + @table_name + ' tmp1 WHERE EXISTS( SELECT * FROM TABLENAME tmp2 WHERE tmp2.id = tmp1 + 1 AND tmp2.System_State = tmp1.System_State AND tmp2.PreviousState = tmp1.PreviousState )' EXEC (@sql)
DECLARE @table_name VARCHAR(100) DECLARE @sql VARCHAR(1000) SET @table_name = '你的表名' SET @sql = 'SELECT * FROM ' + @table_name + ' tmp1 WHERE EXISTS( SELECT * FROM ' + @table_name + ' tmp2 WHERE tmp2.id = tmp1 + 1 AND tmp2.System_State = tmp1.System_State AND tmp2.PreviousState = tmp1.PreviousState )' EXEC (@sql)--假设你有id列,不然我不知道那两个是相邻列
你给的图片没有重复的数据,我在第十四条加了重复数据,给你一个例子你回头试试 if object_id('[tb1]') is not null drop table [tb1] go create table tb1 (System_State varchar(50),PreviousState varchar(50),col int ) insert into tb1 select Null,'New','1' union all select Null,'New','2' union all select 'New','Approved','3' union all select 'Approved','New','4' union all select 'New','Approved','5' union all select 'New','Approved','6' union all select 'New','Approved','7' union all select 'New','Approved','8' union all select 'New','Approved','9' union all select 'Approved','Commited','10'union all select 'Approved','Commited','11'union all select 'Approved','Commited','12'union all select 'Commited','Approved','13'union all select 'Commited','Commited','14'union all select 'Commited','Approved','15' select * from tb1 where col in (case when System_State=PreviousState then -1 else col end)--System_State PreviousState col --NULL New 1 --NULL New 2 --New Approved 3 --Approved New 4 --New Approved 5 --New Approved 6 --New Approved 7 --New Approved 8 --New Approved 9 --Approved Commited 10 --Approved Commited 11 --Approved Commited 12 --Commited Approved 13 --Commited Approved 15
假设你的 Sys_Rev 是连续的 SELECT * FROM 表 WHERE NOT EXISTS(SELECT * FROM 表 T WHERE T.Sys_Rev = 表.Sys_Rev-1 AND T.System_State = 表.System_State AND T.PreviousState = 表.PreviousState) ORDER BY Sys_Rev
select System_Rev,System_State,PreviousState,dense_rank() over(partition by System_State,PreviousState order by System_Rev) as SortId into #temp from tablename select identity(int,2,1) as id, SortId into #temp1 from #temp select * from tablename where System_Rev in (select a.System_Rev from #temp a left join #temp1 b on a.SortId=b.SortId and a.System_Rev=b.id where b.SortId is null )上面的方法可以试一下
只比较System_State 以及 PreviousState这两个字段。
DECLARE @sql VARCHAR(1000)
SET @table_name = '你的表名'
SET @sql = 'SELECT * FROM ' + @table_name + ' tmp1 WHERE EXISTS(
SELECT * FROM TABLENAME tmp2 WHERE tmp2.id = tmp1 + 1 AND tmp2.System_State = tmp1.System_State AND tmp2.PreviousState = tmp1.PreviousState
)'
EXEC (@sql)
DECLARE @sql VARCHAR(1000)
SET @table_name = '你的表名'
SET @sql = 'SELECT * FROM ' + @table_name + ' tmp1 WHERE EXISTS(
SELECT * FROM ' + @table_name + ' tmp2 WHERE tmp2.id = tmp1 + 1 AND tmp2.System_State = tmp1.System_State AND tmp2.PreviousState = tmp1.PreviousState
)'
EXEC (@sql)--假设你有id列,不然我不知道那两个是相邻列
if object_id('[tb1]') is not null drop table [tb1]
go
create table tb1 (System_State varchar(50),PreviousState varchar(50),col int )
insert into tb1
select Null,'New','1' union all
select Null,'New','2' union all
select 'New','Approved','3' union all
select 'Approved','New','4' union all
select 'New','Approved','5' union all
select 'New','Approved','6' union all
select 'New','Approved','7' union all
select 'New','Approved','8' union all
select 'New','Approved','9' union all
select 'Approved','Commited','10'union all
select 'Approved','Commited','11'union all
select 'Approved','Commited','12'union all
select 'Commited','Approved','13'union all
select 'Commited','Commited','14'union all
select 'Commited','Approved','15'
select * from tb1 where col in (case when System_State=PreviousState then -1 else col end)--System_State PreviousState col
--NULL New 1
--NULL New 2
--New Approved 3
--Approved New 4
--New Approved 5
--New Approved 6
--New Approved 7
--New Approved 8
--New Approved 9
--Approved Commited 10
--Approved Commited 11
--Approved Commited 12
--Commited Approved 13
--Commited Approved 15
而不是同一条记录两个字段比较。。
SELECT *
FROM 表
WHERE NOT EXISTS(SELECT *
FROM 表 T
WHERE T.Sys_Rev = 表.Sys_Rev-1
AND T.System_State = 表.System_State
AND T.PreviousState = 表.PreviousState)
ORDER BY Sys_Rev
select System_Rev,System_State,PreviousState,dense_rank() over(partition by System_State,PreviousState order by System_Rev) as SortId into #temp from tablename
select identity(int,2,1) as id, SortId into #temp1 from #temp
select * from tablename where System_Rev in (select a.System_Rev from #temp a left join #temp1 b on a.SortId=b.SortId and a.System_Rev=b.id where b.SortId is null )上面的方法可以试一下
观察到 Sys_Rev 是连续的,那么用 Sys_Rev 判相邻是很正常的考虑。