create table #tb(a int,b int, c int) insert #tb select 1,2,3 union all select 1,2,4 union all select 1,2,5 union all select 1,3,3 union all select 1,3,5;with T as (select Row_number()over(partition by a,b order by c) as num,* from #tb) delete T where num<>1select * from #tb--a b c ------------- ----------- ----------- --1 2 3 --1 3 3
delete t from tb t where exists (select 1 from tb where a=t.a and b=t.b and c>t.c)
create table tb(a int,b int, c int) insert tb select 1,2,3 union all select 1,2,4 union all select 1,2,5 union all select 1,3,3 union all select 1,3,5delete t from tb t where exists (select 1 from tb where b=t.b and c<t.c)select * from tb drop table tb 3楼貌似写反了- -
3楼针对c列不重复可以,如果c列有重复值是不能用的! delete t from tb t where exists (select 1 from tb where b=t.b and c<t.c)select distinct a,b,c into #tb from tbdelete from tbinsert into tb select * from #tbdrop table #tb
select A,B,max(C)C from T group by A,B
create table #tb(a int,b int, c int) insert #tb select 1,2,3 union all select 1,2,4 union all select 1,2,5 union all select 1,3,3 union all select 1,3,5 delete #tb from (select a,b,min(c)c from #tb group by a,b)tb where #tb.a=tb.a and #tb.b=tb.b and #tb.c<>tb.c select * from #tb /* a b c ----------- ----------- ----------- 1 2 3 1 3 3(所影响的行数为 2 行)*/
select A,B,min(C)C from T group by A,B 这就够了,效率最高。原表删除,留查询出的数据就可以拉
create table #tb(a int,b int, c int)
insert #tb
select 1,2,3 union all
select 1,2,4 union all
select 1,2,5 union all
select 1,3,3 union all
select 1,3,5;with T as (select Row_number()over(partition by a,b order by c) as num,* from #tb)
delete T where num<>1select * from #tb--a b c
------------- ----------- -----------
--1 2 3
--1 3 3
delete t
from tb t
where exists (select 1 from tb where a=t.a and b=t.b and c>t.c)
insert tb
select 1,2,3 union all
select 1,2,4 union all
select 1,2,5 union all
select 1,3,3 union all
select 1,3,5delete t
from tb t
where exists (select 1 from tb where b=t.b and c<t.c)select * from tb
drop table tb
3楼貌似写反了- -
3楼针对c列不重复可以,如果c列有重复值是不能用的!
delete t
from tb t
where exists (select 1 from tb where b=t.b and c<t.c)select distinct a,b,c
into #tb
from tbdelete from tbinsert into tb select * from #tbdrop table #tb
select A,B,max(C)C
from T
group by A,B
create table #tb(a int,b int, c int)
insert #tb
select 1,2,3 union all
select 1,2,4 union all
select 1,2,5 union all
select 1,3,3 union all
select 1,3,5
delete #tb
from (select a,b,min(c)c from #tb group by a,b)tb
where #tb.a=tb.a and #tb.b=tb.b and #tb.c<>tb.c select * from #tb
/*
a b c
----------- ----------- -----------
1 2 3
1 3 3(所影响的行数为 2 行)*/
select A,B,min(C)C
from T
group by A,B
这就够了,效率最高。原表删除,留查询出的数据就可以拉