月度报表用SQL如何进行简化

y1983y

方法是达到了，但有点繁琐，望大侠指点，谢谢1
Sub MonthFind()
    Dim cnn As Object, Sql As String
    Dim iRow As Integer
    Set cnn = CreateObject("adodb.connection")
        cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=yes;imex=1';data source=" & ThisWorkbook.FullName
    With Sheet4
        .[a6:i65536].ClearContents
        Sql = "select [件号],[名称],[计划价],sum(数量),sum(金额),0,0,0,0 from [入库登记$a5:l65536] where month(入库日期) < '" & Month(Sheet3.[j4]) & "' group by 件号,名称,计划价"
        Sql = "select * from (" & Sql & ") where [件号] is not null"
            .Range("a6").CopyFromRecordset cnn.Execute(Sql)
        Sql = "select [件号],[名称],[计划价],-sum(数量),-sum(金额),0,0,0,0 from [出库登记$a5:n65536] where month(出库日期) < '" & Month(Sheet3.[j4]) & "' group by 件号,名称,计划价"
            iRow = .[a65536].End(xlUp).Row + 1
        Sql = "select * from (" & Sql & ") where [件号] is not null"
            .Range("a" & iRow).CopyFromRecordset cnn.Execute(Sql)
        Sql = "select [件号],[名称],[计划价],0,0,sum(数量),sum(金额),0,0 from [入库登记$a5:l65536] where month(入库日期) = '" & Month(Sheet3.[j4]) & "' group by 件号,名称,计划价"
            iRow = .[a65536].End(xlUp).Row + 1
        Sql = "select * from (" & Sql & ") where [件号] is not null"
            .Range("a" & iRow).CopyFromRecordset cnn.Execute(Sql)
        Sql = "select [件号],[名称],[计划价],0,0,0,0,sum(数量),sum(金额) from [出库登记$a5:n65536] where month(出库日期) = '" & Month(Sheet3.[j4]) & "' group by 件号,名称,计划价"
            iRow = .[a65536].End(xlUp).Row + 1
        Sql = "select * from (" & Sql & ") where [件号] is not null"
            .Range("a" & iRow).CopyFromRecordset cnn.Execute(Sql)
            End With
    With Sheet3
            .[a6:i65536].ClearContents
        Sql = "select [件号],[名称],[计划价],sum(期初库存),sum(期初金额),sum(本期入库),sum(入库金额),sum(本期出库),sum(出库金额) from [Temp$a5:i65536]where [件号] is not null  group by 件号,名称,计划价"
            .Range("a6").CopyFromRecordset cnn.Execute(Sql)
    End With
End Sub

grf1973

1. Sub MonthFind()
   
2.     Dim cnn As Object, Sql As String, yf
   
3.     Set cnn = CreateObject("adodb.connection")
   
4.     cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=yes;imex=1';data source=" & ThisWorkbook.FullName
   
5.     yf = Month(Sheet3.[j4])
   
6.     With Sheet3
   
7.         .[a6:i65536].ClearContents
   
8.         Sql = "select [件号],[名称],[计划价],sum(数量) as d,sum(金额) as e,0 as f,0 as g,0 as h ,0 as i from [入库登记$a5:l65536] where month(入库日期) < '" & yf & "' group by 件号,名称,计划价 union all " _
   
9.            & "select [件号],[名称],[计划价],-sum(数量) as d,-sum(金额) as e,0 as f,0 as g,0 as h ,0 as i from [出库登记$a5:n65536] where month(出库日期) < '" & yf & "' group by 件号,名称,计划价 union all " _
   
10.            & "select [件号],[名称],[计划价],0 as d,0 as e,sum(数量) as f,sum(金额) as g,0 as h,0 as i  from [入库登记$a5:l65536] where month(入库日期) = '" & yf & "' group by 件号,名称,计划价  union all " _
    
11.            & "select [件号],[名称],[计划价],0 as d,0 as e,0 as f ,0 as g,sum(数量) as h ,sum(金额) as i from [出库登记$a5:n65536] where month(出库日期) = '" & yf & "' group by 件号,名称,计划价"
    
12.        Sql = "select [件号],[名称],[计划价],sum(d),sum(e),sum(f),sum(g),sum(h),sum(i) from (" & Sql & ") where [件号] is not null  group by 件号,名称,计划价"
    
13.        .[a6].CopyFromRecordset cnn.Execute(Sql)
    
14.     End With
    
15. End Sub
    

复制代码

y1983y

grf1973 发表于 2018-8-6 16:33

太酷，太厉害了，我试几次都没成功，一直报错。谢谢！

grf1973

没报错啊，哪里错了？

grf1973

还可以简化一点。

1. Sub MonthFind()
   
2.     Dim cnn As Object, Sql As String, yf%, tj1$, tj2$
   
3.     Set cnn = CreateObject("adodb.connection")
   
4.     cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=yes;imex=1';data source=" & ThisWorkbook.FullName
   
5.     yf = Month(Sheet3.[j4])
   
6.     tj1 = "(iif(month(入库日期)<" & yf & ",1,0))"
   
7.     tj2 = "(iif(month(出库日期)<" & yf & ",1,0))"
   
8.     With Sheet3
   
9.        .[a6:i65536].ClearContents
   
10.        Sql = "select 件号,名称,计划价,数量*" & tj1 & " as d,金额*" & tj1 & " as e,数量*(1-" & tj1 & ") as f,金额*(1-" & tj1 & ") as g,0 as h,0 as i from [入库登记$a5:l] union all " _
    
11.            & "select 件号,名称,计划价,-数量*" & tj2 & " as d,-金额*" & tj2 & " as e,0 as f,0 as g,数量*(1-" & tj2 & ") as h,金额*(1-" & tj2 & ") as i from [出库登记$a5:n]"
    
12.       Sql = "select 件号,名称,计划价,sum(d),sum(e),sum(f),sum(g),sum(h),sum(i) from (" & Sql & ") where 件号 is not null  group by 件号,名称,计划价"
    
13.        .[a6].CopyFromRecordset cnn.Execute(Sql)
    
14.     End With
    
15. End Sub

复制代码

y1983y

grf1973 发表于 2018-8-7 15:13
还可以简化一点。

太厉害了，谢谢！给您点赞！

y1983y

grf1973 发表于 2018-8-7 14:31
没报错啊，哪里错了？

没有错，是我自己改了几回代码，均出错，呵呵

chengshanming

grf1973 发表于 2018-8-6 16:33

大师好！如果不是按月度查询 而是按直接按日期来查询，应该怎么来表达，不会sql,请指教，谢谢！

grf1973

yf写成  "# " & sheet3.[j4] & " #"
日期比较用 入库日期 < & yf & ...... 试试

chengshanming

grf1973 发表于 2018-10-11 11:28
yf写成  "# " & sheet3.[j4] & " #"
日期比较用 入库日期 < & yf & ...... 试试

还是有以下报错！不知道什么原因？

1. Sub MonthFind()
   
2.     Dim cnn As Object, Sql As String, yf, i&
   
3.     Set cnn = CreateObject("adodb.connection")
   
4.     cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=yes;imex=1';data source=" & ThisWorkbook.FullName
   
5.     yf = "# " & Sheet3.[j4] & " #"
   
6.     With Sheet3
   
7.         .[a6:i65536].ClearContents
   
8.         Sql = "select [件号],[名称],[计划价],sum(数量) as d,sum(金额) as e,0 as f,0 as g,0 as h ,0 as i from [入库登记$a5:l65536] where 入库日期 < & yf &  group by 件号,名称,计划价 union all " _
   
9.            & "select [件号],[名称],[计划价],-sum(数量) as d,-sum(金额) as e,0 as f,0 as g,0 as h ,0 as i from [出库登记$a5:n65536] where 出库日期 <  & yf &  group by 件号,名称,计划价 union all " _
   
10.            & "select [件号],[名称],[计划价],0 as d,0 as e,sum(数量) as f,sum(金额) as g,0 as h,0 as i  from [入库登记$a5:l65536] where 入库日期 =  & yf &  group by 件号,名称,计划价  union all " _
    
11.            & "select [件号],[名称],[计划价],0 as d,0 as e,0 as f ,0 as g,sum(数量) as h ,sum(金额) as i from [出库登记$a5:n65536] where 出库日期 =  & yf &  group by 件号,名称,计划价"
    
12.        Sql = "select [件号],[名称],[计划价],sum(d),sum(e),sum(f),sum(g),sum(h),sum(i) from (" & Sql & ") where [件号] is not null  group by 件号,名称,计划价"
    
13.        .[a6].CopyFromRecordset cnn.Execute(Sql)
    
14.    For i = 6 To .[a65536].End(xlUp).Row
    
15.        .Cells(i, "j") = .Cells(i, "d") + .Cells(i, "f") - .Cells(i, "h")
    
16.       .Cells(i, "k") = .Cells(i, "e") + .Cells(i, "g") - .Cells(i, "i")
    
17.        Next
    
18.   End With
    
19. End Sub
    
20. 
    
21. 
    

复制代码