查询的语句select * from t where (A,B) in (select A,B from t group by A,B having count(*) > 1) 加一列自增长列 id 后删除的语句如下 select * from t where (A,B) in (select A,B from t group by A,B having count(*) > 1) and id not in (select min(id) from t group by A,B having count(*) > 1)
--drop table rtcreate table rt(a varchar(10),b varchar(10))insert into rt select 'a1', 'b1' union all select 'a1', 'b1' union all select 'a1', 'b2' ;with t as ( select *, row_number() over(partition by a,b order by a) rownum from rt ) delete from t where rownum = 2 select * from rt /* a b a1 b1 a1 b2 */
create table rt(a varchar(10),b varchar(10)) insert into rt select 'a1', 'b1' union all select 'a1', 'b1' union all select 'a1', 'b2'select distinct * into #temp from rt truncate table rt insert into rt select * from #temp drop table #temp select * from rt/* a1 b1 a1 b2*/
where (A,B) in (select A,B from t group by A,B having count(*) > 1)
加一列自增长列 id 后删除的语句如下
select * from t
where (A,B) in (select A,B from t group by A,B having count(*) > 1)
and id not in (select min(id) from t group by A,B having count(*) > 1)
--drop table rtcreate table rt(a varchar(10),b varchar(10))insert into rt
select 'a1', 'b1' union all
select 'a1', 'b1' union all
select 'a1', 'b2'
;with t
as
(
select *,
row_number() over(partition by a,b order by a) rownum
from rt
)
delete from t
where rownum = 2
select *
from rt
/*
a b
a1 b1
a1 b2
*/
create table rt(a varchar(10),b varchar(10))
insert into rt
select 'a1', 'b1' union all
select 'a1', 'b1' union all
select 'a1', 'b2'select distinct * into #temp from rt
truncate table rt
insert into rt
select * from #temp
drop table #temp
select * from rt/*
a1 b1
a1 b2*/