在EXCEL中使用SQL语句查询集锦-持续更新中，敬请关注

ROBERTCHENTPE

请教老师 能帮房看一看吗？   如何用EXCEL VBA SQL语句删除表中多余的重复记录（多个字段）只保留一条

Option Explicit

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sqls As String
Dim i As Integer

Sub SQLDELEDUPLICATERECORD()
    '' Excel VBA SQL 删除表中多余的重复记录多个字段）只保留一条
    SQLTEST = "SQLTEST.xls"
    Workbooks(SQLTEST).Worksheets(1).Activate
    MYPath = "D:\Users\Administrator\Documents\"
    DBSFullName = MYPath + SQLTEST
    If cnn.State = adStateOpen Then
        cnn.Close
        Set cnn = Nothing
    End If
   
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & DBSFullName  ''ThisWorkbook.FullName
    If cnn.State = adStateOpen Then
        '' 以下两种 SQL语句都失败 请教如何修改
        sqls = ""
        sqls = "delete from [Sheet1$] a "
        sqls = "where (a.PurchaseOrderNo,a.OrderDate) in (select PurchaseOrderNo,OrderDate from [Sheet1$] group by PurchaseOrderNo,OrderDate having count(*) > 1) "
        sqls = "and PurchaseOrderNo not in (select min(PurchaseOrderNo) from [Sheet1$] group by PurchaseOrderNo,OrderDate having count(*)>1)"
        cnn.Execute sqls
        
        sqls = ""
        sqls = "delete * from [Sheet1$] "                                                  ''      <--- all other fields, except PurchaseOrderNo --->
        sqls = "where PurchaseOrderNo NOT in (select min(PurchaseOrderNo) from [Sheet1$] group by IDNo,OrderDate,OrderFirmCode,BuyerName,BuyerEmail,SupplierNo,PaymentTermsNET,FreightTerms,Transportation,ShipVia,Destination)"  ''' having count(*)>1
        cnn.Execute sqls
    End If
    If cnn.State = adStateOpen Then
        cnn.Close
        Set cnn = Nothing
    End If
    If rst.State Then rst.Close
    If rst.State = 1 Then rst.Close
    Set rst = Nothing
End Sub

ROBERTCHENTPE

yz_zax

SQL   mark

opiona

ROBERTCHENTPE 发表于 2017-7-19 18:25
请教老师 能帮房看一看吗？   如何用EXCEL VBA SQL语句删除表中多余的重复记录（多个字段）只保留一条
...

换个思路：
提取全部不重复数据
删除原来所有数据
粘贴提取部分

dawzs

留个记号，学习了

yz_zax

SQL 集锦  MARK

ROBERTCHENTPE

两个excel文件同时SQL查询 失败 ： Syntax error in from clause. 请老师 帮忙解惑
' 搜寻 商品名称,数量 当A.xls 与 B.xls 两者 单价相同
'测试修改 B.xls  B1 单价 为 102
商品名称        数量        单价        金额
B1        10        102        1020
A.xls
商品名称        数量        单价        金额
A1        1        102        102

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sql As String
Sub huizong02()
    Set cnn = CreateObject("adodb.connection")
    If cnn.State = adStateOpen Then     ''
        cnn.Close
        Set cnn = Nothing
    End If
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;hdr=yes;';Data Source=" & ThisWorkbook.Path & "\A.xls"
    If cnn.State = adStateOpen Then
        sql = "select a.商品名称, a.数量 from [sheet1$] as a inner join select b.单价 from [Excel 8.0;Database=" & ThisWorkbook.Path & "\B.xls" & "].[sheet1$] as b on a.单价=b.单价"
        If rst.State Then rst.Close
        rst.Open sql, cnn, 1, 1
        If rst.RecordCount > 0 Then
            MsgBox "SQL DONE"
        End If
    End If
    If cnn.State = adStateOpen Then     ''
        cnn.Close
        Set cnn = Nothing
    End If
    If rst.State Then rst.Close
    If rst.State = 1 Then rst.Close
    Set rst = Nothing
End Sub

ROBERTCHENTPE

SQL工作簿之间的数据查询操作
' 研读 多个excel文件同时SQL查询示例 测试成功 感谢
Sub huizong()
    Set cnn = CreateObject("adodb.connection")
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;hdr=yes;';Data Source=" & ThisWorkbook.Path & "\A.xls"
    With Sheets("sheet1")
        r = .Range("a65536").End(xlUp).Row      '' union all  , a.单价
        sql = " select * from [sheet1$] union all select * from [Excel 8.0;Database=" & ThisWorkbook.Path & "\B.xls" & "].[sheet1$] "
        If r > 2 Then .Range("a2:h100").ClearContents
        .Range("a2").CopyFromRecordset cnn.Execute(sql)
    End With
End Sub

两个excel文件同时SQL查询 失败 ： Syntax error in from clause.

taxan

附件中的示例，注释大于代码，良心贴啊~谢谢，受教了。

jonson694959

大神，受我一拜，正好在学习，找不到相关资料，非常感谢。

nblhj

Mark Mark Mark