现在有一个表A,其中字段a1,a2
我想让a1,a2成为复合主键,所以想清除表中的冗余记录例如表中有记录:
a1, a2 ...
AA01 1
AA01 2
AA01 3
AA02 1
AA02 1 *
AA03 1
AA03 2
AA03 3
那么我必须得先清除打星号的那一行(AA02 1...)数据,才能将a1,a2做成复合主键,请问该如何写这个SQL?
我想让a1,a2成为复合主键,所以想清除表中的冗余记录例如表中有记录:
a1, a2 ...
AA01 1
AA01 2
AA01 3
AA02 1
AA02 1 *
AA03 1
AA03 2
AA03 3
那么我必须得先清除打星号的那一行(AA02 1...)数据,才能将a1,a2做成复合主键,请问该如何写这个SQL?
declare @table table(a1 varchar(20),a2 varchar(10))
insert into @table
select 'AA01','1' union all
select 'AA01','2' union all
select 'AA01','3' union all
select 'AA02','1' union all
select 'AA02','1 *' union all
select 'AA03','1' union all
select 'AA03','2' union all
select 'AA03','3' SELECT a1+a2,* FROM @table WHERE ISNUMERIC(a2)=1
------------result:
/*
AA011 AA01 1
AA012 AA01 2
AA013 AA01 3
AA021 AA02 1
AA031 AA03 1
AA032 AA03 2
AA033 AA03 3
*/
insert into @table
select 'AA01',1 union all
select 'AA01',2 union all
select 'AA01',3 union all
select 'AA02',1 union all
select 'AA02',1 union all
select 'AA03',1 union all
select 'AA03',2 union all
select 'AA03',3
declare @i int
set @i=0
update @table set a2=@i,@i=@i+1 where a1='AA02'
select * from @table
delete @table where a1='AA02' and a2=2
select * from @table
/*
(8 行受影响)(2 行受影响)
a1 a2
-------------------- ----------
AA01 1
AA01 2
AA01 3
AA02 1
AA02 2
AA03 1
AA03 2
AA03 3(8 行受影响)(1 行受影响)a1 a2
-------------------- ----------
AA01 1
AA01 2
AA01 3
AA02 1
AA03 1
AA03 2
AA03 3(7 行受影响)*/
AA02 1
AA02 1
因此我想清除类似这样的重复行。
对于a1,a2列,这里的假设是可能有很多重复对,比如说总共有1万条数据,我想找出其中的<a1,a2>重复的记录,然后把重复的记录删除。
delete from A where id in
(SELECT max(id) as id FROM A group by a1,a2 having count(*)>1)