库存计算SQL语句优化

willin2000

如何修改附件中的SQL语句,满足以下2个要求:
1. 我希望出库中不需要输入"出库位置",在库存表中还是能得出"位置",因为出库位置是由"名称,型号,注册证,规格,单位,有效日期"确定的,是唯一的.
2.在"库存"表中增加"领用人"列.

1. SELECT a.名称,a.型号,a.注册证,a.规格,a.单位,a.有效日期,a.入库,b.出库,a.入库-iif(B.出库 is null,0,B.出库) as 库存,a.位置 from
   
2. (SELECT 名称,型号,注册证,规格,单位,有效日期,sum(入库数量) AS 入库,入库位置 as 位置 FROM [入库$]  
   
3. group by 名称,型号,注册证,规格,单位,有效日期,入库位置) a
   
4. left join (SELECT 名称,型号,注册证,规格,单位,有效日期,sum(领用数量) as 出库,出库位置 as 位置 FROM
   
5. [出库$] group by 名称,型号,注册证,规格,单位,有效日期,出库位置) b
   
6. on  (a.名称=b.名称 or a.名称 is null) and (a.型号=b.型号 or a.型号 is null) and (a.注册证=b.注册证 or a.注册证 is null) and (a.规格=b.规格 or a.规格 is null) and (a.单位=b.单位 or a.单位 is null) and (a.有效日期=b.有效日期 or a.有效日期 is null)
   
7. where a.名称<>null
   
8. ORDER BY a.名称,a.有效日期

复制代码

出入库登记管理.zip (41.64 KB, 下载次数: 16)

2022-2-17 15:37 上传

点击文件名下载附件
阅读权限: 1

zhgdy

SELECT a.名称,a.型号,a.注册证,a.规格,a.单位,a.有效日期,a.入库,b.出库,a.入库-iif(B.出库 is null,0,B.出库) as 库存,a.位置,b.领用人 from (SELECT 名称,型号,注册证,规格,单位,有效日期,sum(入库数量) AS 入库,入库位置 as 位置 FROM [入库$]   group by 名称,型号,注册证,规格,单位,有效日期,入库位置) a left join (SELECT 名称,型号,注册证,规格,单位,有效日期,sum(领用数量) as 出库,领用人 FROM [出库$] group by 名称,型号,注册证,规格,单位,有效日期,领用人) b on  (a.名称=b.名称 or a.名称 is null) and (a.型号=b.型号 or a.型号 is null) and (a.注册证=b.注册证 or a.注册证 is null) and (a.规格=b.规格 or a.规格 is null) and (a.单位=b.单位 or a.单位 is null) and (a.有效日期=b.有效日期 or a.有效日期 is null) where a.名称<>null ORDER BY a.名称,a.有效日期

个人觉得你库存里有领用人没意义，例如：不是一次性把入库总数量领出，存在多次、不同人领出的情况，查询结果没用。

willin2000

zhgdy 发表于 2022-2-18 10:26
SELECT a.名称,a.型号,a.注册证,a.规格,a.单位,a.有效日期,a.入库,b.出库,a.入库-iif(B.出库 is null,0,B. ...

谢谢，语句无法使用,提示缺少参数.
把领用时间和领用人都放进库存中（不同时间不同人分开记录），这样怎么写？
感觉会比较难。

zhgdy

查询代码时没有问题的，有问题的时连接的字符串，原来你用的时绝对地址，附件里我用VBA做了个相对地址的。你看一看 出入库登记管理.rar (78.67 KB, 下载次数: 13)

2022-3-1 08:59 上传

点击文件名下载附件
描述

willin2000

zhgdy 发表于 2022-3-1 09:01
查询代码时没有问题的，有问题的时连接的字符串，原来你用的时绝对地址，附件里我用VBA做了个相对地址的。 ...

非常感谢, 打开时自动更新连接文件的宏非常实用.
我把文件名也改成连接自己了. 以防文件名改了找不到.

1. Private Sub Workbook_Open()
   
2. 
   
3. Sheets("库存").Select
   
4. With ActiveWorkbook.Connections(1).ODBCConnection
   
5. .Connection = Array(Array("ODBC;DSN=Excel Files;DBQ=" & ThisWorkbook.Path & "" & ThisWorkbook.Name & "; DefaultDir=" _
   
6. & ThisWorkbook.Path & "\;DriverId=1046;MaxBu"), Array("fferSize=2048;PageTimeout=5;"))
   
7. End With
   
8. End Sub

复制代码

willin2000

zhgdy 发表于 2022-3-1 09:01
查询代码时没有问题的，有问题的时连接的字符串，原来你用的时绝对地址，附件里我用VBA做了个相对地址的。 ...

谢谢,打开时自动更新文件名录非常实用.

我加入的文件名的更改

1. Private Sub Workbook_Open()
   
2. 
   
3. Sheets("库存").Select
   
4. With ActiveWorkbook.Connections(1).ODBCConnection
   
5. .Connection = Array(Array("ODBC;DSN=Excel Files;DBQ=" & ThisWorkbook.Path & "" & ThisWorkbook.Name & "; DefaultDir=" _
   
6. & ThisWorkbook.Path & "\;DriverId=1046;MaxBu"), Array("fferSize=2048;PageTimeout=5;"))
   
7. End With
   
8. End Sub

复制代码

willin2000

zhgdy 发表于 2022-3-1 09:01
查询代码时没有问题的，有问题的时连接的字符串，原来你用的时绝对地址，附件里我用VBA做了个相对地址的。 ...

非常感谢,打开文件时自动更新目录的宏非常实用

willin2000

zhgdy 发表于 2022-3-1 09:01
查询代码时没有问题的，有问题的时连接的字符串，原来你用的时绝对地址，附件里我用VBA做了个相对地址的。 ...

下面的语句运行错误代码13,提示类型不匹配,应该怎么改?

1. Private Sub Workbook_Open()
   
2. 'With ActiveWorkbook.Connections("出入库登记管理新").OLEDBConnection
   
3.     Sheets("库存").Select
   
4.     With ActiveWorkbook.Connections(1).ODBCConnection
   
5.     .Connection = Array(Array("Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=D:\库存信息统计 工作版.xlsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;IMEX=1"";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False"))
   
6. End With
   
7. End Sub
   

复制代码

willin2000

willin2000 发表于 2022-3-4 11:50
谢谢,打开时自动更新文件名录非常实用.

我加入的文件名的更改

1. Private Sub Workbook_Open()
   
2. 'With ActiveWorkbook.Connections("出入库登记管理新").OLEDBConnection
   
3.     Sheets("库存").Select
   
4.     With ActiveWorkbook.Connections(1).ODBCConnection
   
5.     .Connection = Array(Array("Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=D:\库存信息统计 工作版.xlsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;IMEX=1"";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False"))
   
6. End With
   
7. End Sub
   

复制代码

这个语句运行显示错误代码13,提示类型匹配错误,应该怎么改.