sql语句批量删除符合条件的重复记录？

rzz8118

要求按照月份、个人代码保留一个记录，删除重复记录？

zhaogang1960

思路
一、创建临时表：
    SQL = "select first(id) as id,月份,first(金额) as 金额,个人代码 from Sheet1 group by 月份,个人代码"
    SQL = "select * into 临时表 from (" & SQL & ")"
二、删除数据表 Sheet1
SQL = "drop table Sheet1"
三、用临时表创建数据表Sheet1
SQL = "select * into Sheet1 from 临时表"
四、删除临时表
SQL = "drop table 临时表"

zhaogang1960

程序如下：

1. Sub sql语句批量删除符合条件的重复记录()
   
2.     Dim cnn As Object, SQL$
   
3.     Set cnn = CreateObject("ADODB.Connection")
   
4.     'cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\Database2.accdb"'excelVBA使用
   
5.     cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & CurrentProject.FullName'accessVBA使用
   
6.     SQL = "select first(id) as id,月份,first(金额) as 金额,个人代码 from Sheet1 group by 月份,个人代码"
   
7.     SQL = "select * into 临时表 from (" & SQL & ")"
   
8.     cnn.Execute SQL
   
9.     SQL = "drop table Sheet1"
   
10.     cnn.Execute SQL
    
11.     SQL = "select * into Sheet1 from 临时表"
    
12.     cnn.Execute SQL
    
13.     SQL = "drop table 临时表"
    
14.     cnn.Execute SQL
    
15.     MsgBox "OK"
    
16.     cnn.Close
    
17.     Set cnn = Nothing
    
18. End Sub
    

复制代码

amazeyeli

如果这算重复记录的话,那你的表有主键么???

select distinct col2,col3,col4,id=row_number()over(order by getdate()) into table_new from table_old;

or

create table table_new
(
id int indentity(1,1),
col2 int,
col3 money,
col4 int
);
insert into table_new
select select distinct col2,col3,col4
from table_old;

Renco

如果id字段的索引是唯一性的话：
delete Sheet1.* From Sheet1 Where id<>(Select Top 1 A.id From Sheet1 As A Where A.月份=Sheet1.月份 And A.金额=Sheet1.金额 And A.个人代码=Sheet1.个人代码 Order By id);

rzz8118

id字段不是唯一的，，科目代码和个人编码是唯一的，这个是从用友辅助表里面导出的数据