if object_id('tempdb..#a')is not null drop table #a go create table #a(ID_1 int, ID_2 int,a nvarchar(10),b nvarchar(10),c nvarchar(10)) go insert into #a select 1,0,'Come','cn','no' union all select 1,1,'On','en','no' union all select 6,0,'Come','cn','no' union all select 6,1,'On','en','no' union all select 23,0,'hello','en','ok' union all select 23,1,'+','en','ok' union all select 23,2,'world','en','ok' union all select 44,0,'hello','en','ok' union all select 44,1,'+','en','ok' union all select 44,2,'world','en','ok' union all select 180,0,'hello','en','ok' union all select 180,1,'+','en','ok' union all select 180,2,'world','en','ok' union all select 180,3,'+','en','ok' union all select 191,0,'hello','en','ok' union all select 191,1,'+','en','ok' union all select 200,0,'hello','en','no' union all select 200,1,'+','en','ok' union all select 200,2,'world','en','ok' union all select 205,0,'Come','cn','no' union all select 207,1,'On','en','ok' go --select * from #a;with cte as( select count(id_1) over (partition by id_1) counum, dense_rank() over (partition by 1 order by id_1) ranum, row_number() over (partition by id_1 order by getdate()) ronum ,* from #a ),twoc as ( select count(id_1) over (partition by id_1) cccoum, * from( select tt.* from cte cc join cte tt on cc.a=tt.a and cc.b=tt.b and cc.c=tt.c and cc.counum=tt.counum and cc.ronum=tt.ronum and cc.ranum<tt.ranum and cc.ranum= (select min(ranum) from cte dd where dd.counum =tt.counum) )a ) --want Result select * from #a Except( select ID_1,ID_2,a,b,c from twoc where cccoum=counum) /* ID_1 ID_2 a b c ----------- ----------- ---------- ---------- ---------- 1 0 Come cn no 1 1 On en no 23 0 hello en ok 23 1 + en ok 23 2 world en ok 180 0 hello en ok 180 1 + en ok 180 2 world en ok 180 3 + en ok 191 0 hello en ok 191 1 + en ok 200 0 hello en no 200 1 + en ok 200 2 world en ok 205 0 Come cn no 207 1 On en ok(16 行受影响) */ -- 需要删除的 --select ID_1,ID_2,a,b,c from twoc where cccoum=counum
go
create table #a(ID_1 int, ID_2 int,a nvarchar(10),b nvarchar(10),c nvarchar(10))
go
insert into #a
select 1,0,'Come','cn','no' union all
select 1,1,'On','en','no' union all
select 6,0,'Come','cn','no' union all
select 6,1,'On','en','no' union all
select 23,0,'hello','en','ok' union all
select 23,1,'+','en','ok' union all
select 23,2,'world','en','ok' union all
select 44,0,'hello','en','ok' union all
select 44,1,'+','en','ok' union all
select 44,2,'world','en','ok' union all
select 180,0,'hello','en','ok' union all
select 180,1,'+','en','ok' union all
select 180,2,'world','en','ok' union all
select 180,3,'+','en','ok' union all
select 191,0,'hello','en','ok' union all
select 191,1,'+','en','ok' union all
select 200,0,'hello','en','no' union all
select 200,1,'+','en','ok' union all
select 200,2,'world','en','ok' union all
select 205,0,'Come','cn','no' union all
select 207,1,'On','en','ok'
go
--select * from #a;with cte as(
select count(id_1) over (partition by id_1) counum,
dense_rank() over (partition by 1 order by id_1) ranum,
row_number() over (partition by id_1 order by getdate()) ronum ,*
from #a
),twoc as (
select count(id_1) over (partition by id_1) cccoum, * from(
select tt.* from cte cc join cte tt on cc.a=tt.a and cc.b=tt.b
and cc.c=tt.c and cc.counum=tt.counum and cc.ronum=tt.ronum
and cc.ranum<tt.ranum and cc.ranum=
(select min(ranum) from cte dd where dd.counum =tt.counum) )a
)
--want Result
select * from #a Except( select ID_1,ID_2,a,b,c from twoc where cccoum=counum)
/*
ID_1 ID_2 a b c
----------- ----------- ---------- ---------- ----------
1 0 Come cn no
1 1 On en no
23 0 hello en ok
23 1 + en ok
23 2 world en ok
180 0 hello en ok
180 1 + en ok
180 2 world en ok
180 3 + en ok
191 0 hello en ok
191 1 + en ok
200 0 hello en no
200 1 + en ok
200 2 world en ok
205 0 Come cn no
207 1 On en ok(16 行受影响)
*/
-- 需要删除的
--select ID_1,ID_2,a,b,c from twoc where cccoum=counum