向Sql高手请求帮助

那些年松雅湖畔

求一条进销存sql语句:已知期初,入库,出库数据,
求库存!

模拟数据详见附件!

huang1314wei

1. Sub test()   
   
2.     sql_1 = "(Select 商品名称,商品代码,sum(入库数量),sum(入库金额) from 入库 group by 商品名称,商品代码) as B"
   
3.     sql_2 = "(Select 商品名称,商品代码,-sum(出库数量),-sum(出库金额) from 出库 group by 商品名称,商品代码) as C"
   
4.     sql = "Select A.商品名称,A.商品代码,A.期初数量,A.期初金额,A.销售单价,B.入库数量,B.入库金额,C.销售数量,C.销售金额,(A.期初数量+B.入库数量+C.销售数量) as 库存数量,（(A.期初金额+B.入库金额+C.销售金额)/(A.期初数量+B.入库数量+C.销售数量)) as 库存单价,(A.期初金额+B.入库金额+C.销售金额) as 库存金额"
   
5.     sql = sql & " from 期初 A inner join " & sql_1 & " on A.商品名称=B.商品名称 and A.商品代码=B.商品代码 inner join " & sql_2 & " on A.商品名称=C.商品名称 and A.商品代码=C.商品代码"
   
6. End Sub

复制代码

那些年松雅湖畔

感谢版主的解答!

那些年松雅湖畔

huang1314wei 发表于 2019-3-14 18:17

版主能否发个完整附件,我这边测试未能通过!

huang1314wei

那些年松雅湖畔 发表于 2019-3-15 09:36
版主能否发个完整附件,我这边测试未能通过!

如果测试不通过的话，可以把字段加上中刮号，一般excel里面执行SQL最好是加上，避免不必要的麻烦

魂断蓝桥

库存单价,库存金额没做，没看懂如何计算的。

Sub a()
Dim cnn, myf$, sql$, SQA, SQB, SQC
myf = ThisWorkbook.FullName
Set cnn = CreateObject("adodb.connection")
cnn.Open "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties='Excel 12.0;HDR=YES'; Data Source=" & myf
SQA = "SELECT  商品名称,商品代码 FROM [期初$A1:B] UNION SELECT  商品名称,商品代码 FROM [入库$A1:B] WHERE 商品名称 is not null"
sql = "select A.*,B.期初数量,B.期初金额,B.销售单价,C.入库数量,C.入库金额,D.销售数量,D.销售金额 from (((" & SQA & ") AS A LEFT JOIN [期初$A1:E] AS B ON A.商品名称=B.商品名称 AND A.商品代码=B.商品代码)"
sql = sql & "  LEFT JOIN [入库$A1:E] AS C ON A.商品名称=C.商品名称 AND A.商品代码=C.商品代码) " _
             & " LEFT JOIN [出库$A1:D] AS D ON A.商品名称=D.商品名称 AND A.商品代码=D.商品代码"
sql = "SELECT 商品名称,商品代码,期初数量,期初金额,销售单价,入库数量,入库金额,销售数量,销售金额, IIF(ISNULL(期初数量),0,期初数量)+入库数量-销售数量 FROM (" & sql & ")"
[A2:L9999] = ""
Range("A2").CopyFromRecordset cnn.Execute(sql)
Set cnn = Nothing
End Sub

那些年松雅湖畔

魂断蓝桥 发表于 2019-3-15 10:44
库存单价,库存金额没做，没看懂如何计算的。

Sub a()

感谢解答,
库存金额=期初金额+入库金额-销售金额
库存单价=库存金额/库存数量

那些年松雅湖畔

魂断蓝桥 发表于 2019-3-15 10:44
库存单价,库存金额没做，没看懂如何计算的。

Sub a()

老师如能考虑多批次入库和出库就完美了!

huang1314wei

那些年松雅湖畔 发表于 2019-3-15 09:36
版主能否发个完整附件,我这边测试未能通过!

1. Sub Test4()
   
2.     Dim Conn As Object, Rst As Object
   
3.     Dim strConn As String, strSQL As String
   
4.     Dim i As Integer, PathStr As String
   
5.     Set Conn = CreateObject("ADODB.Connection")
   
6.     Set Rst = CreateObject("ADODB.Recordset")
   
7.     PathStr = ThisWorkbook.FullName   '设置工作簿的完整路径和名称
   
8.     Select Case Application.Version * 1    '设置连接字符串,根据版本创建连接
   
9.     Case Is <= 11
   
10.         strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & PathStr
    
11.     Case Is >= 12
    
12.         strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & PathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
    
13.     End Select
    
14.     '设置SQL查询语句
    
15.     sql_1 = "(Select [商品名称],[商品代码],sum([入库数量]) as [入库数量],sum([入库金额]) as [入库金额] from [入库$] group by [商品名称],[商品代码]) B"
    
16.     sql_2 = "(Select [商品名称],[商品代码],sum([销售数量]) as [销售数量],sum([销售金额]) as [销售金额] from [出库$] group by [商品名称],[商品代码]) C"
    
17.     strSQL = "Select A.[商品名称],A.[商品代码],A.[期初数量],A.[期初金额],A.[销售单价],B.[入库数量],B.[入库金额],C.[销售数量],C.[销售金额],(A.[期初数量]+B.[入库数量]-C.[销售数量]) as [库存数量],((A.[期初金额]+B.[入库金额]-C.[销售金额])/(A.[期初数量]+B.[入库数量]-C.[销售数量])) as [库存单价],(A.[期初金额]+B.[入库金额]-C.[销售金额]) as [库存金额]"
    
18.     strSQL = strSQL & " from ([期初$] A inner join " & sql_1 & " on A.[商品名称]=B.[商品名称] and A.[商品代码]=B.[商品代码]) inner join " & sql_2 & " on A.[商品名称]=C.[商品名称] and A.[商品代码]=C.[商品代码]"
    
19.     Conn.Open strConn    '打开数据库链接
    
20.     Set Rst = Conn.Execute(strSQL)    '执行查询，并将结果输出到记录集对象
    
21.     With Sheet7
    
22.         .Cells.Clear
    
23.         For i = 0 To Rst.Fields.Count - 1    '填写标题
    
24.             .Cells(1, i + 1) = Rst.Fields(i).Name
    
25.         Next i
    
26.         .Range("A2").CopyFromRecordset Rst
    
27.         .Cells.EntireColumn.AutoFit  '自动调整列宽
    
28.         .Cells.EntireColumn.AutoFit  '自动调整列宽
    
29.     End With
    
30.     Rst.Close    '关闭数据库连接
    
31.     Conn.Close
    
32.     Set Conn = Nothing
    
33.     Set Rst = Nothing
    
34. End Sub
    
35. 
    

复制代码

huang1314wei

上面那个有问题，其实应该以入库表作为参照表，这样没有期初但是有入库的商品也能显示库存