原库存-生产用量=现库存

fsc163198

用SQl做到　原库存-生产用量=现库存　按原库存颜色排序原库存没有的颜色为负数添加到现库存
原库存-生产用量=现库存.rar (12.58 KB, 下载次数: 1223)

2011-11-16 14:43 上传

点击文件名下载附件

fsc163198

能做到 原库存-用量=现库存  不排序现库存内有所有的 颜色 数据 这样也行

fsc163198

1. Sub SQL计算现库()
   
2.   Dim cnn As New ADODB.Connection
   
3.   Dim rs As New ADODB.Recordset
   
4.   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
   
5.       strSQL = "SELECT A.颜色,(A.短厚L线-iif(isnull(B.短厚L线),0,B.短厚L线))as 短厚L线,(A.长厚L线-iif(isnull(B.长厚L线),0,B.长厚L线))as 长厚L线,(A.短薄L线-iif(isnull(B.短薄L线),0,B.短薄L线))as 短薄L线,(A.长薄L线-iif(isnull(B.长薄L线),0,B.长薄L线))as 长薄L线 FROM [L线库存运算$A2:E40] AS A Left  JOIN [L线库存运算$G2:K40] AS B ON B.颜色=A.颜色"
   
6.   rs.Open (strSQL), cnn, adOpenKeyset, adLockReadOnly
   
7.   For Each Field In rs.Fields
   
8.       [S2].Offset(0, i) = Field.Name
   
9.        i = i + 1
   
10.   Next
    
11. Sheets("L线库存运算").Range("S3").CopyFromRecordset rs
    
12. Set rs = Nothing
    
13. Set cnn = Nothing
    
14. End Sub

复制代码

INNER JOIN匹配返回行 Left  JOIN 左表返回所有的行  RIGHT JOIN 右表返回所有的行  如何能做到两表所有关键行？

fsc163198

汗! 弄了两天 结果是出来了 还是不理想  请高手指点

1. Sub SQL计算现库存()
   
2.   Dim CNN As New ADODB.Connection
   
3.   Dim rs As New ADODB.Recordset
   
4.   CNN.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
   
5.   strSQL = "SELECT * FROM (SELECT A.颜色,(A.短厚L线-iif(isnull(B.短厚L线),0,B.短厚L线))as 短厚L线,(A.长厚L线-iif(isnull(B.长厚L线),0,B.长厚L线))as 长厚L线,(A.短薄L线-iif(isnull(B.短薄L线),0,B.短薄L线))as 短薄L线,(A.长薄L线-iif(isnull(B.长薄L线),0,B.长薄L线))as 长薄L线 FROM [L线库存运算$A2:E40] AS A Left  JOIN [L线库存运算$G2:K40] AS B ON B.颜色=A.颜色)  union SELECT * FROM (SELECT B.颜色,(iif(isnull(A.短厚L线),0,A.短厚L线)-B.短厚L线)as 短厚L线,(iif(isnull(A.长厚L线),0,A.长厚L线)-B.长厚L线)as 长厚L线,(iif(isnull(A.短薄L线),0,A.短薄L线)-B.短薄L线)as 短薄L线,(iif(isnull(A.长薄L线),0,A.长薄L线)-B.长薄L线)as 长薄L线 FROM [L线库存运算$A2:E40] AS A RIGHT  JOIN [L线库存运算$G2:K40] AS B ON B.颜色=A.颜色)"
   
6.   rs.Open (strSQL), CNN, adOpenKeyset, adLockReadOnly
   
7.   For Each Field In rs.Fields
   
8.       [S2].Offset(0, i) = Field.Name
   
9.        i = i + 1
   
10.   Next
    
11. Range("S3").CopyFromRecordset rs
    
12. Set rs = Nothing
    
13. Set CNN = Nothing
    
14. End Sub

复制代码

fsc163198

{:soso_e113:} 搞定了　数组和SQL计算结果一模一样

1. Sub SQL计算现库()
   
2.   Dim cnn As New ADODB.Connection
   
3.   Dim rs As New ADODB.Recordset
   
4.   X = Range("A65536").End(xlUp).Row
   
5.   Y = Range("G65536").End(xlUp).Row
   
6.   Range("S3:W40").ClearContents
   
7.   cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties='Excel 8.0;hdr=no;imex=1';data source=" & ThisWorkbook.FullName
   
8.     For i = 2 To 5
   
9.   XCM = XCM & ",(iif(isnull(A.f" & i & "),0,A.f" & i & ")-iif(isnull(B.f" & i & "),0,B.f" & i & "))as f" & i
   
10.   Next
    
11.   strSQL = "SELECT A.f1" & XCM & " FROM [L线库存运算$A3:E" & X & "] AS A Left  JOIN [L线库存运算$G3:K" & Y & "] AS B ON A.f1=B.f1 UNION ALL select *  from (SELECT B.f1" & XCM & " FROM [L线库存运算$A3:E" & X & "] AS A Right JOIN [L线库存运算$G3:K" & Y & "] AS B ON A.f1=B.f1) AS C WHERE NOT EXISTS (SELECT 1 FROM (SELECT * FROM [L线库存运算$A3:E" & X & "])AS D where C.f1=D.f1)"
    
12.   rs.Open (strSQL), cnn, adOpenKeyset, adLockReadOnly
    
13. Range("S3").CopyFromRecordset rs
    
14. Set rs = Nothing
    
15. Set cnn = Nothing
    
16. End Sub

复制代码

jiminyanyan

fsc163198 发表于 2011-12-1 19:55
搞定了　数组和SQL计算结果一模一样

学习了。。。

fsc163198

ftfox 发表于 2011-12-7 15:59
有意思，自己出问题自己解决，呵呵

{:soso_e101:} 高手不露 哪自己也不能不想办法解决呀

ftfox

有意思，自己出问题自己解决，呵呵