请帮忙用SQL直接按月份汇总

fsc163198

按月份汇总.rar (9.11 KB, 下载次数: 73)

2011-8-16 18:44 上传

点击文件名下载附件

Sub 按月汇总()
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
conn.Open
If conn.State = adStateOpen Then
    sSql = "Select distinct 客户 From [销售记录$A1:C65536] where 合计 > 1"
    Cells(2, 1).CopyFromRecordset conn.Execute(sSql)
    conn.Close
End If
Set conn = Nothing
For i = 1 To 12
    Dim AdoCn As New ADODB.Connection, Rst As New ADODB.Recordset
    AdoCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=Yes;';Data Source=" & ThisWorkbook.FullName
    Rst.Open "Select Cnt From [月份汇总$a1:a100] As A Left Join (Select 客户,Sum(合计) As Cnt From [销售记录$A1:C65536] where 日期 Like'2011-" & i & "%'Group By 客户) B On a.[客户]=b.[客户]", AdoCn
    Cells(2, i + 1).CopyFromRecordset Rst: Rst.Close
    AdoCn.Close: Set Rst = Nothing: Set AdoCn = Nothing
Next i
End Sub

Gemmy

1. Sub °´Ô»ã×Ü()
   
2. Set conn = New ADODB.Connection
   
3. Sub 按月汇总()
   
4. Set conn = New ADODB.Connection
   
5. conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
   
6. conn.Open
   
7. If conn.State = adStateOpen Then
   
8.     sSql = "Select distinct 客户 From [销售记录$A1:C65536] where 合计 > 1"
   
9.     Cells(2, 1).CopyFromRecordset conn.Execute(sSql)
   
10.     conn.Close
    
11. End If
    
12. Set conn = Nothing
    
13. For i = 1 To 12
    
14.     Dim AdoCn As New ADODB.Connection, Rst As New ADODB.Recordset
    
15.     AdoCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=Yes;';Data Source=" & ThisWorkbook.FullName
    
16.     Rst.Open "Select Cnt From [月份汇总$a1:a100] As A Left Join (Select 客户,Sum(合计) As Cnt From [销售记录$A1:C65536] where Month(日期)= " & i & " Group By 客户) B On a.[客户]=b.[客户]", AdoCn
    
17.     Cells(2, i + 1).CopyFromRecordset Rst: Rst.Close
    
18.     AdoCn.Close: Set Rst = Nothing: Set AdoCn = Nothing
    
19. Next i
    
20. End Sub

复制代码

Rst.Open "Select Cnt From [月份汇总$a1:a100] As A Left Join (Select 客户,Sum(合计) As Cnt From [销售记录$A1:C65536] where Month(日期)= " & i & " Group By 客户) B On a.[客户]=b.[客户]", AdoCn

roych

不过日期类型貌似该用#号来处理（除非不是标准日期格式），LS的代码没测试过，不知道直接用“=”行不行。
Rst.Open "Select Cnt From [月份汇总$a1:a100] As A Left Join (Select 客户,Sum(合计) As Cnt From [销售记录$A1:C65536] where Month(日期)=#" & i & "# Group By 客户) B On a.[客户]=b.[客户]", AdoCn