34,838
社区成员




if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (id int,parentid int,type nvarchar(6),name nvarchar(2))
insert into [tb]
select 1,1,'正常','a' union all
select 2,1,'不正常','b' union all
select 3,1,'不正常','c' union all
select 4,2,'正常','d' union all
select 5,2,'不正常','e' union all
select 6,2,'不正常','f'
select * from [tb]
select * from TB T where exists(select 1 from tb where parentid = T.parentid and id >t.id ) and type ='不正常'
/*
id parentid type name
2 1 不正常 b
5 2 不正常 e*/
select * from tbx as a
where type='不正常' and
ID=(select MIN(ID) from tbx where parentId=a.parentId and type=a.type
)
select * from tbx as a where type='不正常'
and not exists(select 1 from tbx where parentId=a.parentId and type=a.type and ID<a.ID)
select * from tbx a
where id=(select min(tbx) from tbx where parentid=a.parentid and type='不正常')
order by id