SQL层层左联接，为什么数据不能相加减

handsomcao

SQL层层左联接，为什么数据不能相加减

1. 
   
2. Sub 汇总2()
   
3.     Dim cnn As Object, rs As Object, SQL$
   
4.     Set cnn = CreateObject("Adodb.Connection")
   
5.     Set rs = CreateObject("Adodb.Recordset")
   
6.     cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ActiveWorkbook.FullName
   
7.    
   
8.    
   
9.     SQL1 = "select  '入库' as 单据类型,品号,辅助列,物料名称,规格型号,单位,数量,仓库 from [入帐$a:k] where 品号 is not null"
   
10.     SQL2 = "select  '出库' as 单据类型,品号,辅助列,物料名称,规格型号,单位,数量,仓库 from [出帐$a:k] where 品号 is not null"
    
11.     SQL3 = "select  单据类型,辅助列,物料名称,规格型号,单位,sum(数量) as 不合入 from (" & SQL1 & ") where 仓库 = '不合格品仓' group by 单据类型,辅助列,物料名称,规格型号,单位"
    
12.     SQL4 = "select  单据类型,辅助列,物料名称,规格型号,单位,sum(数量) as 不合出 from (" & SQL2 & ") where 仓库 = '不合格品仓' group by 单据类型,辅助列,物料名称,规格型号,单位"
    
13.     SQL5 = "select  单据类型,辅助列,物料名称,规格型号,单位,sum(数量) as 良品入 from (" & SQL1 & ") where 仓库 = '配件仓' group by 单据类型,辅助列,物料名称,规格型号,单位"
    
14.     SQL6 = "select  单据类型,辅助列,物料名称,规格型号,单位,sum(数量) as 良品出 from (" & SQL2 & ") where 仓库 = '配件仓' group by 单据类型,辅助列,物料名称,规格型号,单位"
    
15.    
    
16.     SQL7 = "select  a.辅助列,a.物料名称,a.规格型号,a.单位,a.期初,b.不合入  from [物料表$a:k]a left join (" & SQL3 & ")b on a.辅助列=b.辅助列"
    
17.     SQL8 = "select  a.辅助列,a.物料名称,a.规格型号,a.单位,a.期初,a.不合入,b.不合出  from (" & SQL7 & ")a left join (" & SQL4 & ")b on a.辅助列=b.辅助列"
    
18.     SQL9 = "select  a.辅助列,a.物料名称,a.规格型号,a.单位,a.期初,a.不合入,a.不合出,b.良品入  from (" & SQL8 & ")a left join (" & SQL5 & ")b on a.辅助列=b.辅助列"
    
19.     SQL10 = "select  a.辅助列,a.物料名称,a.规格型号,a.单位,a.期初,a.良品入,b.良品出,a.不合入,a.不合出 from (" & SQL9 & ")a left join (" & SQL6 & ")b on a.辅助列=b.辅助列"
    
20.     SQL = "select  辅助列,物料名称,规格型号,单位,期初,良品入,良品出,不合入,不合出,([期初] + 良品入 - 良品出) as 结存 from (" & SQL10 & ")"
    
21.    
    
22.    
    
23. 
    
24.     rs.Open SQL, cnn, 1, 3
    
25.     Range("a2:j10000").ClearContents
    
26.     Range("a2").CopyFromRecordset rs
    
27.     rs.Close
    
28.     cnn.Close
    
29.     Set rs = Nothing
    
30.     Set cnn = Nothing
    
31. End Sub
    

复制代码

总帐.rar (365.94 KB, 下载次数: 11)

2015-4-10 11:24 上传

点击文件名下载附件