进销存报表查询统计的几种sql语句

示例说明：本示例是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查询编辑器里面或记事本里面进行阅读。

总结：若数据量不大的情况下，两种方式的查询速度相差不大，若数据量大，推荐使用方式二，方式二比方式一查询速度快，代码也相对简单