删除重复列

假设表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