示例说明:本示例是SqlServer数据库查询药品的进销存数据
1、表说明
Inigoodsstocks 期初库存表:存放使用系统之前的原有商品库存数量和金额,可以理解为结转库存
BillsDetail 单据明细表:存放各商品进销存明细数据(注意,我表里面出的数据存的是负数,入的存的是正数)
Store 库存表:存放各商品库存数量和金额
Commodity 商品表:存放商品资料数据
CommodityCode 商品编码表:存放商品编码,价格,单位等数据
Warehouse 仓库表:存放仓库资料数据
Suppliers 供应商表:存放供应商资料数据
2、注意事项
因为我BillsDetail单据明细表里面存放的有正负数(入:正数,出:负数),如果你存的都是正数,要自行处理一下:需把各出库数据转换成负数进行计算。期初时间是个固定日期时间,应根据自己的实际期初时间(期初库存结转日期时间)自行调整
3、sql代码
方式一:用WITH关键字将各数据先存临时表,然后再关联查询各临时表数据。
WITH
--先把各数据存到对应临时表,如下面的:qc AS (SELECT P_id,S_id,批号,Supplier_id,SUM(数量) AS 期初数量,SUM(数量*含税价) AS 期初含税金额 FROM dbo.Inigoodsstocks),就是查出期初数据放到qc这个临时表里面
qc AS (SELECT P_id,S_id,批号,Supplier_id,SUM(数量) AS 期初数量,SUM(含税金额) AS 期初含税金额 FROM dbo.Inigoodsstocks GROUP BY P_id,S_id,批号,Supplier_id),--期初数据汇总
sq AS ( --上期数据汇总
SELECT P_id,S_id,批号,Supplier_id,SUM(数量) AS 上期数量,SUM(含税金额) AS 上期含税金额
FROM
(SELECT P_id,S_id,批号,Supplier_id,SUM(数量) AS 数量,SUM(含税成本金额) AS 含税金额
FROM dbo.BillsDetail --各单据明细表
WHERE 单据类型 IN ('采购入库单','采购退货单','销售出库单','销售退货单','报损单','报溢单') AND 记账时间 < '2024-03-01 00:00:00' AND 记账时间 >= '2023-03-01 00:00:00' --注意:2024-03-01 00:00:00是开始时间,2023-03-01 00:00:00是期初时间,应根据你自己的期初时间自行调整
GROUP BY P_id,S_id,批号,Supplier_id
) sqSum
GROUP BY P_id,S_id,批号,Supplier_id
),
bq AS ( --本期数据汇总
SELECT P_id,S_id,批号,Supplier_id,SUM(数量) AS 本期数量,SUM(含税金额) AS 本期含税金额
FROM
(SELECT P_id,S_id,批号,Supplier_id,SUM(数量) AS 数量,SUM(含税成本金额) AS 含税金额
FROM dbo.BillsDetail --各单据明细表
WHERE 单据类型 IN ('采购入库单','采购退货单','销售出库单','销售退货单','报损单','报溢单') AND 记账时间 BETWEEN '2024-03-01 00:00:00' AND '2024-12-31 23:59:59' --注意:2024-03-01 00:00:00是开始时间,2024-12-31 23:59:59是结束时间
GROUP BY P_id,S_id,批号,Supplier_id
) bqSum
GROUP BY P_id,S_id,批号,Supplier_id
),
--分别对本期各单据逐一进行数量和金额汇总
bqcg AS (SELECT P_id,S_id,批号,Supplier_id,SUM(数量) AS 本期采购入库单数量,SUM(含税成本金额) AS 本期采购入库单含税金额 FROM dbo.BillsDetail WHERE 记账时间 BETWEEN '2024-03-01 00:00:00' AND '2024-12-31 23:59:59' AND 单据类型 = '采购入库单' GROUP BY P_id,S_id,批号,Supplier_id), --本期采购入库
bqct AS (SELECT P_id,S_id,批号,Supplier_id,SUM(数量) AS 本期采购退货单数量,SUM(含税成本金额) AS 本期采购退货单含税金额 FROM dbo.BillsDetail WHERE 记账时间 BETWEEN '2024-03-01 00:00:00' AND '2024-12-31 23:59:59' AND 单据类型 = '采购退货单' GROUP BY P_id,S_id,批号,Supplier_id), --本期采购退货
bqxs AS (SELECT P_id,S_id,批号,Supplier_id,SUM(数量) AS 本期销售出库单数量,SUM(含税成本金额) AS 本期销售出库单含税金额 FROM dbo.BillsDetail WHERE 记账时间 BETWEEN '2024-03-01 00:00:00' AND '2024-12-31 23:59:59' AND 单据类型 = '销售出库单' GROUP BY P_id,S_id,批号,Supplier_id),--本期销售出库
bqxt AS (SELECT P_id,S_id,批号,Supplier_id,SUM(数量) AS 本期销售退货单数量,SUM(含税成本金额) AS 本期销售退货单含税金额 FROM dbo.BillsDetail WHERE 记账时间 BETWEEN '2024-03-01 00:00:00' AND '2024-12-31 23:59:59' AND 单据类型 = '销售退货单' GROUP BY P_id,S_id,批号,Supplier_id),--本期销售退货
bqbs AS (SELECT P_id,S_id,批号,Supplier_id,SUM(数量) AS 本期报损单数量,SUM(含税成本金额) AS 本期报损单含税金额 FROM dbo.BillsDetail WHERE 记账时间 BETWEEN '2024-03-01 00:00:00' AND '2024-12-31 23:59:59' AND 单据类型 = '报损单' GROUP BY P_id,S_id,批号,Supplier_id),--本期报损
bqby AS (SELECT P_id,S_id,批号,Supplier_id,SUM(数量) AS 本期报溢单数量,SUM(含税成本金额) AS 本期报溢单含税金额 FROM dbo.BillsDetail WHERE 记账时间 BETWEEN '2024-03-01 00:00:00' AND '2024-12-31 23:59:59' AND 单据类型 = '报溢单' GROUP BY P_id,S_id,批号,Supplier_id),--本期报溢
kc AS (SELECT P_id,S_id,批号,Supplier_id FROM dbo.Store GROUP BY P_id,S_id,批号,Supplier_id) --库存数据
--然后查询上面存了各数据的临时表数据
SELECT * FROM (SELECT sp.P_id,ck.ckid AS S_id,gys.gysid AS Supplier_id,sp.商品名称,sp.规格,sp.剂型,sp.生产厂商,gys.供应商名称,pr.商品编码,pr.单位,kc.批号,ck.仓库名称,
ISNULL(qc.期初数量,0) AS 期初数量,ISNULL(qc.期初含税金额,0) AS 期初含税金额,
ISNULL(qc.期初数量,0)+ISNULL(sq.上期数量,0) AS 上期数量,(ISNULL(qc.期初含税金额,0)+ISNULL(sq.上期含税金额,0)) AS 上期含税金额,
ISNULL(bqcg.本期采购入库单数量,0) AS 采购入库单数量,ISNULL(bqcg.本期采购入库单含税金额,0) AS 采购入库单含税金额,
ISNULL(bqct.本期采购退货单数量,0) AS 采购退货单数量,ISNULL(bqct.本期采购退货单含税金额,0) AS 采购退货单含税金额,
ISNULL(bqxs.本期销售出库单数量,0) AS 销售出库单数量,ISNULL(bqxs.本期销售出库单含税金额,0) AS 销售出库单含税金额,
ISNULL(bqxt.本期销售退货单数量,0) AS 销售退货单数量,ISNULL(bqxt.本期销售退货单含税金额,0) AS 销售退货单含税金额,
ISNULL(bqbs.本期报损单数量,0) AS 报损单数量,ISNULL(bqbs.本期报损单含税金额,0) AS 报损单含税金额,
ISNULL(bqby.本期报溢单数量,0) AS 报溢单数量,ISNULL(bqby.本期报溢单含税金额,0) AS 报溢单含税金额,
ISNULL(qc.期初数量,0)+ISNULL(sq.上期数量,0)+ISNULL(bq.本期数量,0) AS 结存数量,(ISNULL(qc.期初含税金额,0)+ISNULL(sq.上期含税金额,0)+ISNULL(bq.本期含税金额,0)) AS 结存含税金额
FROM kc
LEFT JOIN (SELECT Auto_id AS P_id,商品名称,规格,剂型,生产厂商 FROM dbo.Commodity) sp ON sp.P_id = kc.P_id --商品表
LEFT JOIN (SELECT Product_id,编码 AS 商品编码,单位 FROM dbo.CommodityCode) pr ON pr.Product_id = kc.P_id --商品编码表
LEFT JOIN (SELECT Auto_id AS ckid,名称 AS 仓库名称 FROM dbo.Warehouse) ck ON ck.ckid = kc.S_id --仓库表
LEFT JOIN (SELECT Auto_id AS gysid,名称 AS 供应商名称 FROM dbo.Suppliers) gys ON gys.gysid = kc.Supplier_id --供应商表
LEFT JOIN qc ON qc.P_id = kc.P_id AND qc.批号 = kc.批号 AND qc.S_id = kc.S_id AND qc.Supplier_id = kc.Supplier_id --期初数据
LEFT JOIN sq ON sq.P_id = kc.P_id AND sq.批号 = kc.批号 AND sq.S_id = kc.S_id AND sq.Supplier_id = kc.Supplier_id --上期数据
LEFT JOIN bq ON bq.P_id = kc.P_id AND bq.批号 = kc.批号 AND bq.S_id = kc.S_id AND bq.Supplier_id = kc.Supplier_id --本期数据
LEFT JOIN bqcg ON bqcg.P_id = kc.P_id AND bqcg.批号 = kc.批号 AND bqcg.S_id = kc.S_id AND bqcg.Supplier_id = kc.Supplier_id --本期采购入库
LEFT JOIN bqct ON bqct.P_id = kc.P_id AND bqct.批号 = kc.批号 AND bqct.S_id = kc.S_id AND bqct.Supplier_id = kc.Supplier_id --本期采购退货
LEFT JOIN bqxs ON bqxs.P_id = kc.P_id AND bqxs.批号 = kc.批号 AND bqxs.S_id = kc.S_id AND bqxs.Supplier_id = kc.Supplier_id --本期销售出库
LEFT JOIN bqxt ON bqxt.P_id = kc.P_id AND bqxt.批号 = kc.批号 AND bqxt.S_id = kc.S_id AND bqxt.Supplier_id = kc.Supplier_id --本期销售退货
LEFT JOIN bqbs ON bqbs.P_id = kc.P_id AND bqbs.批号 = kc.批号 AND bqbs.S_id = kc.S_id AND bqbs.Supplier_id = kc.Supplier_id --本期报损
LEFT JOIN bqby ON bqby.P_id = kc.P_id AND bqby.批号 = kc.批号 AND bqby.S_id = kc.S_id AND bqby.Supplier_id = kc.Supplier_id --本期报溢
) jxc
ORDER BY P_id,S_id,批号
方式二:代码相对简单,查询速度快,推荐使用方式二来统计进销存数据
SELECT * FROM
(SELECT kc.P_id,kc.S_id,kc.Supplier_id,pr.商品编码,sp.商品名称,sp.规格,sp.剂型,sp.生产厂商,gys.供应商名称,pr.单位,kc.批号,ck.仓库名称,ISNULL(qc.期初数量,0) AS 期初数量,ISNULL(qc.期初含税金额,0) AS 期初含税金额,
ISNULL(qc.期初数量,0)+ISNULL(sq.上期数量,0) AS 上期数量,ISNULL(qc.期初含税金额,0)+ISNULL(sq.上期含税金额,0) AS 上期含税金额,
ISNULL(采购入库单数量,0) AS 采购入库单数量,ISNULL(采购入库单金额,0) AS 采购入库单含税金额,ISNULL(采购退货单数量,0) AS 采购退货单数量,ISNULL(采购退货单金额,0) AS 采购退货单含税金额,
ISNULL(销售出库单数量,0) AS 销售出库单数量,ISNULL(销售出库单金额,0) AS 销售出库单含税金额,ISNULL(销售退货单数量,0) AS 销售退货单数量,ISNULL(销售退货单金额,0) AS 销售退货单含税金额,
ISNULL(报损单数量,0) AS 报损单数量,ISNULL(报损单金额,0) AS 报损单含税金额,ISNULL(报溢单数量,0) AS 报溢单数量,ISNULL(报溢单金额,0) AS 报溢单含税金额,
ISNULL(qc.期初数量,0)+ISNULL(sq.上期数量,0)+ISNULL(bq.本期数量,0) AS 结存数量,ISNULL(qc.期初含税金额,0)+ISNULL(sq.上期含税金额,0)+ISNULL(bq.本期含税金额,0) AS 结存含税金额
FROM
(SELECT P_id,S_id,批号,Supplier_id FROM dbo.Store GROUP BY P_id,S_id,批号,Supplier_id) kc --库存表
LEFT JOIN (SELECT Auto_id AS P_id,商品名称,规格,剂型,生产厂商 FROM dbo.Commodity) sp ON sp.P_id = kc.P_id --商品表
LEFT JOIN (SELECT Product_id,编码 AS 商品编码,单位 FROM dbo.CommodityCode) pr ON pr.Product_id = kc.P_id --商品编码表
LEFT JOIN (SELECT Auto_id AS ckid,名称 AS 仓库名称 FROM dbo.Warehouse) ck ON ck.ckid = kc.S_id --仓库表
LEFT JOIN (SELECT Auto_id AS gysid,名称 AS 供应商名称 FROM dbo.Suppliers) gys ON gys.gysid = kc.Supplier_id --供应商表
LEFT JOIN --期初数据汇总
(SELECT P_id,S_id,批号,Supplier_id,SUM(数量) AS 期初数量,SUM(含税金额) AS 期初含税金额
FROM dbo.Inigoodsstocks GROUP BY P_id,S_id,批号,Supplier_id) qc
ON qc.P_id = kc.P_id AND qc.批号 = kc.批号 AND qc.S_id = kc.S_id AND qc.Supplier_id = kc.Supplier_id
LEFT JOIN --上期数据汇总
(SELECT P_id,S_id,批号,Supplier_id,SUM(数量) AS 上期数量,SUM(含税成本金额) AS 上期含税金额
FROM dbo.BillsDetail
WHERE 单据类型 IN ('采购入库单','采购退货单','销售出库单','销售退货单','报损单','报溢单') AND 记账时间 < '2024-03-01 00:00:00' AND 记账时间 >= '2023-03-01 00:00:00' --注意:2024-03-01 00:00:00是开始时间,2023-03-01 00:00:00是期初时间,应根据你自己的期初时间自行调整
GROUP BY P_id,S_id,批号,Supplier_id) sq
ON sq.P_id = kc.P_id AND sq.批号 = kc.批号 AND sq.S_id = kc.S_id AND sq.Supplier_id = kc.Supplier_id
LEFT JOIN --本期数据汇总
(SELECT P_id,S_id,批号,Supplier_id,SUM(数量) AS 本期数量,SUM(含税成本金额) AS 本期含税金额
FROM dbo.BillsDetail
WHERE 单据类型 IN ('采购入库单','采购退货单','销售出库单','销售退货单','报损单','报溢单') AND 记账时间 BETWEEN '2024-03-01 00:00:00' AND '2024-12-31 23:59:59'
GROUP BY P_id,S_id,批号,Supplier_id) bq
ON bq.P_id = kc.P_id AND bq.批号 = kc.批号 AND bq.S_id = kc.S_id AND bq.Supplier_id = kc.Supplier_id
LEFT JOIN --分别按本期各单据行转列进行数量汇总
(SELECT P_id,S_id,批号,Supplier_id,
SUM (CASE WHEN 单据类型 = '采购入库单' THEN 数量 ELSE 0 END) AS 采购入库单数量,
SUM (CASE WHEN 单据类型 = '采购退货单' THEN 数量 ELSE 0 END) AS 采购退货单数量,
SUM (CASE WHEN 单据类型 = '销售出库单' THEN 数量 ELSE 0 END) AS 销售出库单数量,
SUM (CASE WHEN 单据类型 = '销售退货单' THEN 数量 ELSE 0 END) AS 销售退货单数量,
SUM (CASE WHEN 单据类型 = '报损单' THEN 数量 ELSE 0 END) AS 报损单数量,
SUM (CASE WHEN 单据类型 = '报溢单' THEN 数量 ELSE 0 END) AS 报溢单数量
FROM dbo.BillsDetail
WHERE 记账时间 BETWEEN '2024-03-01 00:00:00' AND '2024-12-31 23:59:59' GROUP BY P_id,S_id,批号,Supplier_id) sl
ON sl.P_id = kc.P_id AND sl.批号 = kc.批号 AND sl.S_id = kc.S_id AND sl.Supplier_id = kc.Supplier_id
LEFT JOIN --分别按本期各单据行转列进行金额汇总
(SELECT P_id,S_id,批号,Supplier_id,
SUM (CASE WHEN 单据类型 = '采购入库单' THEN 含税成本金额 ELSE 0 END) AS 采购入库单金额,
SUM (CASE WHEN 单据类型 = '采购退货单' THEN 含税成本金额 ELSE 0 END) AS 采购退货单金额,
SUM (CASE WHEN 单据类型 = '销售出库单' THEN 含税成本金额 ELSE 0 END) AS 销售出库单金额,
SUM (CASE WHEN 单据类型 = '销售退货单' THEN 含税成本金额 ELSE 0 END) AS 销售退货单金额,
SUM (CASE WHEN 单据类型 = '报损单' THEN 含税成本金额 ELSE 0 END) AS 报损单金额,
SUM (CASE WHEN 单据类型 = '报溢单' THEN 含税成本金额 ELSE 0 END) AS 报溢单金额
FROM dbo.BillsDetail
WHERE 记账时间 BETWEEN '2024-03-01 00:00:00' AND '2024-12-31 23:59:59' GROUP BY P_id,S_id,批号,Supplier_id) je
ON je.P_id = kc.P_id AND je.批号 = kc.批号 AND je.S_id = kc.S_id AND je.Supplier_id = kc.Supplier_id
) jxc
ORDER BY P_id,S_id,批号
4、验证进销存数据的正确性
查当前日期时间范围,结存数据和你的实时库存数据相等,且上期数据等于你上期的结余数据,说明你的进销存sql代码正确
以上就是我在实际工作中,常用的两种进销存sql语句,仅供参考,大家实际应用中可根据自己的表和数据自行调整。网站代码格式受限,看着有点乱,各位可以粘贴到sql查询编辑器里面或记事本里面进行阅读。
总结:若数据量不大的情况下,两种方式的查询速度相差不大,若数据量大,推荐使用方式二,方式二比方式一查询速度快,代码也相对简单