求一个sql 程序来删除重复值,然后建复合主键。table:custno lineseqno value
_______ ________ _____2343 3 DP
2432 4 VP
.....
(many rows here)
.....2343 3 IP现在要在custnno 和lineseqno上建复合主键。
但是表里现在custno和lineseqno的组合有重复值,如何用SQL程序来除去重复值(要保留第一个重复值,去掉第二个)
比如上面的值,就要删掉2343 3 IP这组,保留2343 3 DP 这组。多谢了,在线等,急
!!
_______ ________ _____2343 3 DP
2432 4 VP
.....
(many rows here)
.....2343 3 IP现在要在custnno 和lineseqno上建复合主键。
但是表里现在custno和lineseqno的组合有重复值,如何用SQL程序来除去重复值(要保留第一个重复值,去掉第二个)
比如上面的值,就要删掉2343 3 IP这组,保留2343 3 DP 这组。多谢了,在线等,急
!!
from tb a
where not exists(select * from tb where a.custno=custno and a.lineseqno=lineseqno and value<a.value)alter table tb
add constraint PK_custno_lineseqno primary key(custno,lineseqno)
go
insert into #tab values(2343,3,'DP')
insert into #tab values(2432,4,'VP')
insert into #tab values(2343,3,'IP')DELETE a from #tab a where exists (select 1 from #tab where custno=a.custno and lineseqno=a.lineseqno ) and value<>(select top 1 value from #tab where custno=a.custno and lineseqno=a.lineseqno)select * from #tab
alter table #tab add constraint pk_#tab primary key (custno,lineseqno)
custno lineseqno value
----------- ----------- -----
2343 3 DP
2432 4 VP(2 行受影响)
好像不好用啊?
from tb a
where not exists(select * from tb where a.custno=custno and a.lineseqno=lineseqno and value <a.value)
_________ _________
23433 224343 5(...1000 rows)24343 5现在不用管其它的column,就是如在custno和lineseqno这对复合栏里有相同值的话,把相同值选出.我用了select * from service_number_extra a where not exists(select * from service_number_extra where a.custno=custno and a.lineseqno=lineseqno )结果是把service_number_extra里的所有值都选出来了,哪里不对呢?
where a.custno = b.custno and a.lineseqno = b.lineseqno)begin
print 'find duplicates'
end
where exists(select 1 from tb b where a.custno=b.custno and a.lineseqno=b.lineseqno and a.value<b.value)
group by custno,lineseqno
having count(1)>1)