假设表t1中有c1、c2、c3列,表结构如下:
Create Table t1
(c1 int null,
c2 int null,
c3 char(10) null)
我们要删除其中c1和c2多余的重复行(只保留一行)的解决方法:
在表中新增加一个标识列,利用它来删除重复记录,代码如下所示
Alter Table t1
ADD key_col INT NOT NULL IDENTITY
Delete From t1
where exists(select *
from t1 as t2
where t2.c1=t1.c1 and t2.c2=t1.c2
and
t2.key_col>t1.key_col)
Alter Table t1
drop column key_col
例子:
Alter Table L1Record ADD key_col INT NOT NULL IDENTITY
Delete From L1Record
where exists(select *
from L1Record as t2
where t2.KNB=L1Record.KNB
and t2.[Sequence]=L1Record.[Sequence]
and t2.SVWTime=L1Record.SVWTime
and t2.ReceiveTime=L1Record.ReceiveTime
and
t2.key_col>L1Record.key_col)
Alter Table L1Record
drop column key_col