ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
12
返回列表 发新帖

[求助] 向Sql高手请求帮助

[复制链接]

TA的精华主题

TA的得分主题

 楼主| 发表于 2019-3-15 12:20 | 显示全部楼层
huang1314wei 发表于 2019-3-15 12:12
上面那个有问题,其实应该以入库表作为参照表,这样没有期初但是有入库的商品也能显示库存

是的,需要先将期初和本期入库型号合并,再以此执行left join 查询!

TA的精华主题

TA的得分主题

发表于 2019-3-15 12:24 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
那些年松雅湖畔 发表于 2019-3-15 12:20
是的,需要先将期初和本期入库型号合并,再以此执行left join 查询!

这样写就完美了,请见代码

  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 B.[商品名称],B.[商品代码],A.[期初数量],A.[期初金额],A.[销售单价],B.[入库数量],B.[入库金额],C.[销售数量],C.[销售金额],(iif(A.[期初数量] is null,0,A.[期初数量])+B.[入库数量]-iif(C.[销售数量] is null,0,C.[销售数量])) as [库存数量],((iif(A.[期初金额] is null,0,A.[期初金额])+B.[入库金额]-iif(C.[销售金额] is null,0,C.[销售金额]))/(iif(A.[期初数量] is null,0,A.[期初数量])+B.[入库数量]-iif(C.[销售数量] is null,0,C.[销售数量]))) as [库存单价],(iif(A.[期初金额] is null,0,A.[期初金额])+B.[入库金额]-iif(C.[销售金额] is null,0,C.[销售金额])) as [库存金额]"
  18.     strSQL = strSQL & " from (" & sql_1 & " left join [期初$] A on A.[商品名称]=B.[商品名称] and A.[商品代码]=B.[商品代码]) left join " & sql_2 & " on B.[商品名称]=C.[商品名称] and B.[商品代码]=C.[商品代码] where B.[商品名称] is not null"
  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
复制代码

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2019-3-15 12:39 | 显示全部楼层
将魂断蓝桥的稍微改一下也可以,但我不知道为什么会多一空行,请内行指教。

  1. Sub a()
  2. Dim cnn, myf$, sql$, SQA, SQB, SQC
  3. myf = ThisWorkbook.FullName
  4. Set cnn = CreateObject("adodb.connection")
  5. cnn.Open "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties='Excel 12.0;HDR=YES'; Data Source=" & myf
  6. SQA = "SELECT  商品名称,商品代码 FROM [期初$A1:B] UNION SELECT  商品名称,商品代码 FROM [入库$A1:B] WHERE 商品名称 is not null"
  7. 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.商品代码)"
  8. sql = sql & "  LEFT JOIN [入库$A1:E] AS C ON A.商品名称=C.商品名称 AND A.商品代码=C.商品代码) " _
  9.              & " LEFT JOIN [出库$A1:D] AS D ON A.商品名称=D.商品名称 AND A.商品代码=D.商品代码"
  10. sql = "SELECT 商品名称,商品代码,期初数量,期初金额,销售单价,入库数量,入库金额,销售数量,销售金额, " _
  11.       & "IIF(ISNULL(期初数量),0,期初数量)+入库数量-IIF(ISNULL(销售数量),0,销售数量) as 库存数量, " _
  12.       & "IIF(ISNULL(期初金额),0,期初金额)+入库金额-IIF(ISNULL(销售金额),0,销售金额) as 库存金额 " _
  13.       & "FROM (" & sql & ")"
  14. sql = "SELECT 商品名称,商品代码,期初数量,期初金额,销售单价,入库数量,入库金额,销售数量,销售金额,库存数量,库存金额/库存数量,库存金额 " _
  15.       & "FROM (" & sql & ")"
  16. Sheet7.Activate
  17. [A2:L9999].Clear
  18. Range("A2").CopyFromRecordset cnn.Execute(sql)
  19. Set cnn = Nothing
  20. End Sub
复制代码

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-28 18:44 , Processed in 0.035973 second(s), 8 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表