求助select查询语句的问题

wang002368

想通过select把表2的数据查询并汇总到表1，目前无法排除空行的干扰，并且表1的B列的结果的顺序也是乱的。sql刚接触，实在是搞不懂。之前用的数组来查询汇总，但现在数据量渐渐增多，运行时间也上来了，想用sql来提提速

wang002368

求大佬们帮忙看看给个思路

zpy2

select * from Sheet2;
select * from Sheet1;
select * from Sheet2 left join
(select f01,sum(f02) from Sheet1 group by f01) using(f01);

tanglf188

1. Sub EX()
   
2.     Dim Cn As Object, StrSQL As String
   
3.     't = Timer
   
4.    
   
5.     Set Cn = CreateObject("Adodb.Connection")
   
6.    
   
7.     Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
   
8.    
   
9.     StrSQL = "Select 号,Sum(额) From [4、表二$]b Where 号 is not null GROUP BY 号"
   
10.    
    
11.     Sheet1.Range("A2").CopyFromRecordset Cn.Execute(StrSQL)
    
12.       
    
13.     'MsgBox Timer - t
    
14. End Sub

复制代码

WANT-T

SQl语句里添加where筛选

wang002368

Sub EX()
    Dim Cn As Object, StrSQL As String
   
    Set Cn = CreateObject("Adodb.Connection")
   
    Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
   
    StrSQL = "select * from [表一$] left join (select [表二$].号,sum([表二$].额) from [表二$] group by [表二$].号) using ([表二$].号)"
   
    Range("B2").CopyFromRecordset Cn.Execute(StrSQL)
End Sub
目前改成这样还是报错，看来我得系统的再看看资料再说了

longwin

Sub EX()
    Dim Cn As Object, StrSQL As String
    't = Timer
   
    Set Cn = CreateObject("Adodb.Connection")
   
    Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
   
    StrSQL = "Select a.号, Sum(b.额) From [3、表一$] a Left Join [4、表二$]b On a.号=b.号 GROUP BY a.号"
   
    Range("a2").CopyFromRecordset Cn.Execute(StrSQL)
      
    'MsgBox Timer - t
End Sub

longwin

如果去掉空号的行改成这样：

StrSQL = "Select a.号, Sum(b.额) From [3、表一$] a Left Join [4、表二$]b On a.号=b.号 GROUP BY a.号  having a.号 <>null "
Range("A2").CopyFromRecordset Cn.Execute(StrSQL)

longwin

代码审核太慢了：

zpy2

wang002368 发表于 2024-7-31 18:01
Sub EX()
    Dim Cn As Object, StrSQL As String
   

On a.号=b.号 GROUP BY a.号"

我这里是sqlite,你要修改一下