declare @tb table (a varchar(10),b varchar(10))
insert into @tb select 'a','b'
insert into @tb select 'b','a'
insert into @tb select 'c','d'
insert into @tb select 'd','c'
insert into @tb select 'x','y'
select * from (
select px=row_number() over (order by a),* from @tb
) tp where px%2<>0px a b
1 a b
3 c d
5 x y
insert into @tb select 'a','b'
insert into @tb select 'b','a'
insert into @tb select 'c','d'
insert into @tb select 'd','c'
insert into @tb select 'x','y'
select * from (
select px=row_number() over (order by a),* from @tb
) tp where px%2<>0px a b
1 a b
3 c d
5 x y
insert into tb select 'a','b'
insert into tb select 'b','a'
insert into tb select 'c','d'
insert into tb select 'd','c'
insert into tb select 'x','y'
alter table tb add px int identity(1,1)
delete tb where px%2=0
alter table tb drop column px
select * from tba b
a b
c d
x y
where name in (select name
from
(select *,col=(select count(1) from t where name<a.name) from t a)a
where a.col%2=0
)
declare @tb table (name varchar(10),b varchar(10))
insert into @tb select 'a','b'
insert into @tb select 'b','a'
insert into @tb select 'c','d'
insert into @tb select 'd','c'
insert into @tb select 'x','y'delete @tb
where name in (select name
from
(select *,col=(select count(1) from @tb where name<a.name) from @tb a)a
where a.col%2<>0
)
select * from @tb/*name b
---------- ----------
a b
c d
x y(所影响的行数为 3 行)*/
但是表中不全是有对应重复数据的,也有X,Y这种只有一条的
select col1,col2 from table1 a join table1 b where a.primarykey<b.primarykey and ....
declare @tb table (name varchar(10),b varchar(10))
insert into @tb select 'a','b'
insert into @tb select 'b','a'
insert into @tb select 'c','d'
insert into @tb select 'd','c'
insert into @tb select 'x','y'delete @tb where name in (select distinct b from @tb)
column1 varchar(1),
column2 varchar(1))
insert into t1 values('a','b')
insert into t1 values('b','a')
insert into t1 values('c','d')
insert into t1 values('d','c')
insert into t1 values('x','y')select * from t1 where column1=column2 or column1<column2
union
select * from t1 as a
where not exists (select * from t1 as b where a.column1=b.column2 and a.column2=b.column1)
where column1>column2
and not exists (select * from t1 as b where column1=b.column2 and column2=b.column1)不好意思!上一次是选择语句,这是删除语句
where column1>column2
and not exists (select * from t1 as b where column1=b.column2 and column2=b.column1)不好意思!上一次是选择语句,这是删除语句
where column1<=column2