select * from tb a where not exists(select 1 from tb where yppm=a.yppm and ggid>a.ggid)
select * from tb a where exists(select 1 from tb where yppm=a.yppm and ggid=a.ggid-1) and not exists(select 1 from tb where yppm=a.yppm and ggid=a.ggid +1)
---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([GGID] int,[yppm] varchar(10)) 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,'归脾丸' union all select 8,'归脾丸' union all select 9,'黄氏响声丸' union all select 10,'黄氏响声丸' union all select 11,'杞菊地黄丸' union all select 12,'杞菊地黄丸'
---查询--- select * from tb t where not exists(select 1 from tb where yppm=t.yppm and ggid=t.ggid+1) and exists(select 1 from tb where yppm=t.yppm and ggid=t.ggid-1) ---结果--- GGID yppm ----------- ---------- 2 六味地黄丸 4 知柏地黄丸 6 补中益气丸 8 归脾丸 10 黄氏响声丸 12 杞菊地黄丸(所影响的行数为 6 行)
select * from tb a where not exists(select 1 from tb where yppm=a.yppm and ggid>a.ggid)
where exists(select 1 from tb where yppm=a.yppm and ggid=a.ggid-1)
and not exists(select 1 from tb where yppm=a.yppm and ggid=a.ggid +1)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([GGID] int,[yppm] varchar(10))
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,'归脾丸' union all
select 8,'归脾丸' union all
select 9,'黄氏响声丸' union all
select 10,'黄氏响声丸' union all
select 11,'杞菊地黄丸' union all
select 12,'杞菊地黄丸'
---查询---
select *
from tb t
where
not exists(select 1 from tb where yppm=t.yppm and ggid=t.ggid+1)
and
exists(select 1 from tb where yppm=t.yppm and ggid=t.ggid-1)
---结果---
GGID yppm
----------- ----------
2 六味地黄丸
4 知柏地黄丸
6 补中益气丸
8 归脾丸
10 黄氏响声丸
12 杞菊地黄丸(所影响的行数为 6 行)