JOIN操作语法错误，求助

saoxu

SQL = "select a.往来单位银行户名,a.产地,a.商品编号,b.赠送规则 sum(a.包数）as 数量 from [销售明细表$] a left join [送包表$f1:h]b on a.往来单位银行户名=b.客户 and a.产地=b.产地 where 赠送规则>'' Group By a.往来单位银行户名,a.产地,a.商品编号,b.赠送规则 " 出错

saoxu

Sub 统计()
    Dim cnn As Object, SQL$
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider = Microsoft.ace.Oledb.12.0;Extended Properties =Excel 12.0;Data Source =" & ThisWorkbook.FullName
    Sheet56.Range("A2:E9999").ClearContents
For i = 2 To Range("F9999").End(xlUp).Row
SQL = " (select 客户,replace(产地,'全部','') as 产地,赠送规则 from [送包表$f1:h])b "
SQL = "select a.往来单位银行户名,a.产地,a.商品编号 ,Sum(a.包数)as 数量,b.赠送规则 from [销售明细表$]a ," & SQL & " where a.往来单位银行户名=b.客户 and instr(a.产地,b.产地)>0  Group By a.往来单位银行户名,a.产地,a.商品编号,b.赠送规则"
    Range("a2").CopyFromRecordset cnn.Execute(SQL)
Next
    cnn.Close
Set cnn = Nothing
End Sub

saoxu

谢谢看见星光老师

看见星光

saoxu 发表于 2018-4-24 15:27
Sub 统计()
    Dim cnn As Object, SQL$
    Set cnn = CreateObject("ADODB.Connection")

Sub 统计()
    Dim cnn As Object, SQL$
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider = Microsoft.ace.Oledb.12.0;Extended Properties =Excel 12.0;Data Source =" & ThisWorkbook.FullName
    [A2:E50000].ClearContents
    SQL = " (select 客户,replace(产地,'所有产地','') as 产地,赠送规则 from [送包表$h1:j])b "
    SQL = "select a.往来单位银行户名,a.产地,a.商品编号,a.包数,b.赠送规则 from [销售明细表$]a ," & SQL & " where a.往来单位银行户名=b.客户 and instr(a.产地,b.产地)>0"
    SQL = "select 往来单位银行户名,产地,商品编号,sum(包数) as 数量,赠送规则 from" & "(" & SQL & ") group by 往来单位银行户名,产地,商品编号,赠送规则"
    Range("a2").CopyFromRecordset cnn.Execute(SQL)
    cnn.Close
    Set cnn = Nothing
End Sub

看见星光

附件： 副本.rar (90.61 KB, 下载次数: 13)

2018-4-24 16:15 上传

点击文件名下载附件

看见星光

saoxu

谢谢呀，这两天真学到知识了，原来SQL可以这样一层层的套用下去。

saoxu

还是有问题，但需要引用第二个规则时，还是不懂这个left join 哪里错了，老显示操作符不对，或者JON语法不对。

saoxu

还有另一个问题，我想依然画葫芦先解决点问题，可是不明白你上面(Left(SQL, Len(SQL) - 10))是什么意思，为什么非得减10

LMY123

saoxu 发表于 2018-4-24 23:29
还有另一个问题，我想依然画葫芦先解决点问题，可是不明白你上面(Left(SQL, Len(SQL) - 10))是什么意思，为 ...

http://club.excelhome.net/forum.php?mod=viewthread&tid=907979&mobile=2