进销存SQL的编写方法是什么？进销存SQL如何撰写

金琼

2025_05_20 12:00:53

阅读30分钟

已读19次

进销存SQL的编写方法，核心在于：1、统一且可扩展的数据模型；2、以“出入库流水+库存余额快照”记账；3、清晰的成本核算（加权平均或FIFO）；4、严格的事务与并发控制；5、以视图/存储过程封装报表与口径。其中“2、出入库流水+库存余额快照”尤为关键：把每一次采购入库、销售出库、调拨、盘盈盘亏都记录为不可变的流水，并定期或触发更新库存余额快照，查询时先取快照再叠加增量流水，既保证准确，又能在大数据量下获得良好性能和可追溯性。

《进销存SQL的编写方法是什么？进销存SQL如何撰写》

一、进销存SQL的总体设计思路

目标导向：进销存系统以商品、仓库为维度，贯穿采购、销售、库存三大流程。SQL的编写应同时满足记账准确、报表口径统一、性能可扩展三重目标。
事件驱动：所有业务动作抽象为“事件”（入库、出库、调拨、退货、盘点），以库存流水（inventory_txn）记录事件明细，再由库存余额（stock_balance/快照）呈现当前状态。
口径统一：所有报表（库存现存量、周转率、采购/销售统计、成本与毛利）从同一数据模型派生，避免重复口径与口径漂移。
成本核算：根据企业管理需要，选择加权平均法或FIFO，必要时提供多口径并存（管理报表用加权平均、财务结账用FIFO），并通过SQL严格定义。
产品化思路：可优先采用成熟模板或低代码平台快速落地，如简道云进销存，官网地址： https://s.fanruan.com/xrxfy;

二、核心数据模型与表结构

模型原则：分清主数据（商品、仓库、供应商、客户）、业务单据（采购/销售订单与出入库单）、流水（inventory_txn）、余额（stock_balance/快照）、成本层（cost_layer，若用FIFO）。
命名规范：统一命名、统一主键、外键完整性、时间戳与审计字段齐全（created_at、updated_at、operator_id）。
计量与币种：商品单位、换算（箱/件/公斤）、多币种与税率字段预留，避免后期改造。

推荐基础表与关键字段如下（可据实际简化或扩展）：

表名	关键字段	说明
product	product_id(PK)、sku、name、uom_id、status	商品主数据
warehouse	warehouse_id(PK)、name、location	仓库主数据
supplier	supplier_id(PK)、name、tax_id	供应商
customer	customer_id(PK)、name、tax_id	客户
purchase_order	po_id(PK)、supplier_id、po_date、status、currency	采购订单头
purchase_order_line	po_line_id(PK)、po_id(FK)、product_id、qty、price、tax_rate	采购订单行
goods_receipt	grn_id(PK)、warehouse_id、grn_date、ref_po_id	采购入库单头
goods_receipt_line	grn_line_id(PK)、grn_id(FK)、product_id、qty、unit_cost	入库单行
sales_order	so_id(PK)、customer_id、so_date、status、currency	销售订单头
sales_order_line	so_line_id(PK)、so_id(FK)、product_id、qty、price、tax_rate	销售订单行
delivery	dn_id(PK)、warehouse_id、dn_date、ref_so_id	销售出库单头
delivery_line	dn_line_id(PK)、dn_id(FK)、product_id、qty	出库单行
inventory_txn	txn_id(PK)、warehouse_id、product_id、txn_type、qty_in、qty_out、unit_cost、src_doc、src_line、txn_time	库存流水，唯一记账源
stock_balance	warehouse_id、product_id、as_of_date、qty_on_hand、avg_cost、last_txn_id	库存余额快照（可用物化视图或表）
cost_layer	layer_id(PK)、warehouse_id、product_id、in_qty、remain_qty、unit_cost、src_grn_line、created_at	FIFO/分层成本记录
uom	uom_id(PK)、code、ratio_base	计量单位与换算

说明：

所有数量建议使用DECIMAL(18,4)或更细的精度，单价/金额用DECIMAL(18,6)避免累计误差。
txn_type建议枚举：‘GRN’入库、‘DELIVERY’出库、‘ADJUST_POS/NEG’盘盈/盘亏、‘TRANSFER_OUT/IN’调拨等。

三、典型查询与写法示例

现存量查询（按商品+仓库）：从快照取期初，再叠加流水增量到指定日期。
销售与采购统计：按时间维度（天/周/月）、客户/供应商维度聚合。
库存周转率：period_sales_qty / avg_stock_qty。
库龄分析：按入库时间或成本层老化分桶。

示例一：某日期的库存现存量（快照+增量）

-- 取快照日期（如每日0点）之后的增量叠加
WITH base AS (
SELECT sb.warehouse_id, sb.product_id, sb.qty_on_hand, sb.as_of_date
FROM stock_balance sb
WHERE sb.as_of_date = DATE '2025-11-01'
),
delta AS (
SELECT it.warehouse_id, it.product_id,
SUM(it.qty_in - it.qty_out) AS delta_qty
FROM inventory_txn it
WHERE it.txn_time > TIMESTAMP '2025-11-01 00:00:00'
AND it.txn_time <= TIMESTAMP '2025-11-19 23:59:59'
GROUP BY it.warehouse_id, it.product_id
)
SELECT COALESCE(b.warehouse_id, d.warehouse_id) AS warehouse_id,
COALESCE(b.product_id, d.product_id) AS product_id,
COALESCE(b.qty_on_hand, 0) + COALESCE(d.delta_qty, 0) AS qty_on_hand
FROM base b
FULL OUTER JOIN delta d
ON b.warehouse_id = d.warehouse_id AND b.product_id = d.product_id;

示例二：月度采购/销售统计与毛利（加权平均口径）

WITH sales AS (
SELECT DATE_TRUNC('month', d.dn_date) AS mth, dl.product_id,
SUM(dl.qty) AS sales_qty, SUM(dl.qty * sol.price) AS sales_amt
FROM delivery d
JOIN delivery_line dl ON dl.dn_id = d.dn_id
JOIN sales_order_line sol ON sol.so_id = d.ref_so_id AND sol.product_id = dl.product_id
GROUP BY DATE_TRUNC('month', d.dn_date), dl.product_id
),
cogs AS (
-- 若使用stock_balance维护avg_cost，可在出库时写入unit_cost=当前avg_cost
SELECT DATE_TRUNC('month', it.txn_time) AS mth, it.product_id,
SUM(it.qty_out * it.unit_cost) AS cogs_amt
FROM inventory_txn it
WHERE it.txn_type = 'DELIVERY'
GROUP BY DATE_TRUNC('month', it.txn_time), it.product_id
)
SELECT s.mth, s.product_id, s.sales_qty, s.sales_amt,
COALESCE(c.cogs_amt, 0) AS cogs_amt,
s.sales_amt - COALESCE(c.cogs_amt, 0) AS gross_profit
FROM sales s
LEFT JOIN cogs c ON s.mth = c.mth AND s.product_id = c.product_id;

示例三：库龄分桶（以入库流水时间计算）

WITH inbound AS (
SELECT product_id, warehouse_id, txn_time, (qty_in - qty_out) AS net_in
FROM inventory_txn
WHERE txn_type IN ('GRN','ADJUST_POS')
),
snap AS (
SELECT warehouse_id, product_id, qty_on_hand
FROM stock_balance
WHERE as_of_date = CURRENT_DATE
),
age AS (
SELECT i.warehouse_id, i.product_id,
CASE
WHEN CURRENT_DATE - DATE(i.txn_time) <= 30 THEN '0-30天'
WHEN CURRENT_DATE - DATE(i.txn_time) <= 90 THEN '31-90天'
WHEN CURRENT_DATE - DATE(i.txn_time) <= 180 THEN '91-180天'
ELSE '180天以上'
END AS age_bucket,
SUM(GREATEST(i.net_in, 0)) AS qty_candidate
FROM inbound i
GROUP BY i.warehouse_id, i.product_id, age_bucket
)
SELECT a.warehouse_id, a.product_id, a.age_bucket,
-- 实际可用量需按FIFO层冲减至现存量；此处为近似分布
ROUND(a.qty_candidate, 4) AS qty_approx
FROM age a
JOIN snap s ON s.warehouse_id = a.warehouse_id AND s.product_id = a.product_id;

四、成本核算SQL：加权平均、FIFO与分层

加权平均法（移动加权）：每次入库更新avg_cost = (库存数量旧均价 + 入库数量入库单价) / 新库存数量；出库按当前avg_cost计成本。
FIFO法：为每次入库生成成本层（cost_layer），出库时从最早未耗尽的层开始扣减，并以该层单价计成本。
分层优势：可实现库龄与成本追溯，对财务要求严谨的企业更适用；加权平均更简单，适合管理报表与高并发业务。

加权平均入库更新示例：

-- 入库确认事务中：
-- 1）写inventory_txn（qty_in，unit_cost=入库单价）
-- 2）更新stock_balance的数量与avg_cost
UPDATE stock_balance sb
SET qty_on_hand = sb.qty_on_hand + :in_qty,
avg_cost = CASE
WHEN (sb.qty_on_hand + :in_qty) = 0 THEN sb.avg_cost
ELSE ROUND( (sb.qty_on_hand * sb.avg_cost + :in_qty * :unit_cost)
/ (sb.qty_on_hand + :in_qty), 6)
END,
last_txn_id = :txn_id
WHERE sb.warehouse_id = :wh AND sb.product_id = :prod;

-- 出库记账：写inventory_txn（qty_out，unit_cost=当前avg_cost），再扣减库存
UPDATE stock_balance
SET qty_on_hand = qty_on_hand - :out_qty,
last_txn_id = :txn_id
WHERE warehouse_id = :wh AND product_id = :prod;

FIFO成本层与出库扣减示例：

-- 入库：新增成本层
INSERT INTO cost_layer (warehouse_id, product_id, in_qty, remain_qty, unit_cost, src_grn_line, created_at)
VALUES (:wh, :prod, :in_qty, :in_qty, :unit_cost, :grn_line_id, NOW());

-- 出库：按层扣减并计算成本（伪SQL，实际需循环或递归CTE）
WITH layers AS (
SELECT layer_id, remain_qty, unit_cost
FROM cost_layer
WHERE warehouse_id = :wh AND product_id = :prod AND remain_qty > 0
ORDER BY created_at ASC, layer_id ASC
),
consume AS (
SELECT layer_id,
LEAST(remain_qty, :out_qty - SUM(LEAST(remain_qty, :out_qty) ) OVER (ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS use_qty,
unit_cost
FROM layers
)
SELECT SUM(use_qty * unit_cost) AS out_cost
FROM consume;

-- 扣减层
UPDATE cost_layer
SET remain_qty = remain_qty - :use_qty
WHERE layer_id = :layer_id AND remain_qty >= :use_qty;

-- 出库流水
INSERT INTO inventory_txn (warehouse_id, product_id, txn_type, qty_in, qty_out, unit_cost, src_doc, src_line, txn_time)
VALUES (:wh, :prod, 'DELIVERY', 0, :out_qty, :weighted_out_cost/:out_qty, :dn_id, :dn_line_id, NOW());

成本方法	优点	缺点	适用场景
加权平均	简洁高效、适合高并发；报表易维护	不能精确反映批次成本与库龄	电商、快消等对批次追溯要求不高
FIFO	精确、可追溯；符合财务细颗粒批次管理	实现复杂、性能压力较大	制造/药品/监管严格行业
标准成本	控制成本波动、便于预算	需要差异分析流程	成本控制严格企业

五、事务、并发与数据一致性

事务隔离：入库/出库必须在一个事务中完成“写流水→更新余额/成本层”。推荐隔离级别REPEATABLE READ或必要时SERIALIZABLE，避免并发下均价与数量错算。
行级锁：更新stock_balance或cost_layer时以warehouse_id+product_id为键获取行锁，防止同一商品同仓并发扣减导致负库存。
负库存保护：在出库更新前检查qty_on_hand >= :out_qty；若必须允许负库存，需在报表口径明确并标注异常。
幂等性：src_doc+src_line在inventory_txn唯一，避免重复记账。
审计字段：记录操作人、来源单据、时间戳；为稽核与追溯提供依据。
回滚策略：任何步骤失败即回滚；出库涉及多层扣减时，全部成功方可提交。

示例：防负库存的保护性更新（乐观锁）

UPDATE stock_balance
SET qty_on_hand = qty_on_hand - :out_qty,
last_txn_id = :txn_id
WHERE warehouse_id = :wh AND product_id = :prod
AND qty_on_hand >= :out_qty;
-- 检查受影响行数=1，否则拒绝并抛错

六、索引、性能与分区

索引策略：
inventory_txn：组合索引(warehouse_id, product_id, txn_time)、单列索引(txn_type)。
stock_balance：主键/唯一(warehouse_id, product_id, as_of_date)或当前余额唯一(warehouse_id, product_id)。
cost_layer：组合索引(warehouse_id, product_id, created_at, remain_qty)。
分区与归档：
inventory_txn按月/季度分区；历史分区归档降低主库压力。
大表仅保留近周期在主库；历史报表走OLAP/归档库。
物化视图与快照：
每日/每小时生成stock_balance快照，查询先取快照再叠加增量。
设置刷新策略：T+1夜间全面刷新，业务高峰用增量计算。
性能优化：
避免跨全库范围计算，所有聚合限定时间窗或商品/仓库范围。
复杂FIFO计算建议转移至存储过程或ETL，结果回写流水单价或成本表。

七、报表与可视化：视图/ETL的封装

统一视图：将常用报表封装为只读视图，供应用与BI复用。
v_stock_current（现存量）
v_purchase_summary（月度采购）
v_sales_summary（月度销售）
v_inventory_turnover（周转率）
v_inventory_aging（库龄）
ETL与数据仓库：将事务库的库存流水与成本层定期抽取至数仓，构建维度模型（商品维、仓库维、时间维）与事实表（入库、出库、库存余额）。
平台支持：如需快速落地或在业务上灵活配置流程与报表，可选用低代码平台的进销存模板，例如简道云进销存，官网地址： https://s.fanruan.com/xrxfy; 其模板可直接使用并支持字段、流程、报表自定义，减少从零写SQL的工作量。

八、常见坑与测试校验清单

退货与红字：区分采购退货（出库，冲减成本层/均价）与销售退货（入库），确保流水方向正确。
单位换算：按商品维度维护UOM及换算率，所有聚合统一转为基准单位。
部分收货/发货：订单与出入库行须一对多映射，不能用简单JOIN误计。
税与币种：报表口径明确是否含税；多币种需汇率日期与来源。
盘点差异：盘盈盘亏记为独立txn_type，并进入成本或管理差异科目。
时点一致性：报表需指定时点（含/不含在途），在途库存需单独维度处理。
双重计数：避免既统计订单又统计出入库造成重复，应以实际出入库为准。
测试数据：构造极端场景（零库存出库、跨库调拨、批量退货、负数修正），逐条核对流水与余额。

九、实战示例：从零到可用的SQL片段

场景：录入采购入库并自动更新库存与均价；随后进行销售出库并计算毛利。

采购入库事务步骤： 1）写入goods_receipt与goods_receipt_line。 2）写入inventory_txn（GRN）。 3）更新stock_balance与avg_cost（或新增cost_layer）。

BEGIN;

INSERT INTO goods_receipt (grn_id, warehouse_id, grn_date, ref_po_id)
VALUES (:grn_id, :wh, CURRENT_DATE, :po_id);

INSERT INTO goods_receipt_line (grn_line_id, grn_id, product_id, qty, unit_cost)
VALUES (:grn_line_id, :grn_id, :prod, :qty, :unit_cost);

INSERT INTO inventory_txn (txn_id, warehouse_id, product_id, txn_type, qty_in, qty_out, unit_cost, src_doc, src_line, txn_time)
VALUES (:txn_id, :wh, :prod, 'GRN', :qty, 0, :unit_cost, :grn_id, :grn_line_id, NOW());

-- 加权平均更新
UPDATE stock_balance sb
SET qty_on_hand = sb.qty_on_hand + :qty,
avg_cost = CASE
WHEN (sb.qty_on_hand + :qty) = 0 THEN sb.avg_cost
ELSE ROUND( (sb.qty_on_hand * sb.avg_cost + :qty * :unit_cost) / (sb.qty_on_hand + :qty), 6)
END,
last_txn_id = :txn_id
WHERE sb.warehouse_id = :wh AND sb.product_id = :prod;

COMMIT;

销售出库事务步骤： 1）写入delivery与delivery_line。 2）根据当前avg_cost或FIFO层计算成本。 3）写入inventory_txn（DELIVERY）并扣减库存。

BEGIN;

INSERT INTO delivery (dn_id, warehouse_id, dn_date, ref_so_id)
VALUES (:dn_id, :wh, CURRENT_DATE, :so_id);

INSERT INTO delivery_line (dn_line_id, dn_id, product_id, qty)
VALUES (:dn_line_id, :dn_id, :prod, :qty);

-- 读取当前均价
SELECT avg_cost INTO :avg_cost
FROM stock_balance WHERE warehouse_id = :wh AND product_id = :prod FOR UPDATE;

-- 防负库存
UPDATE stock_balance
SET qty_on_hand = qty_on_hand - :qty,
last_txn_id = :txn_id
WHERE warehouse_id = :wh AND product_id = :prod
AND qty_on_hand >= :qty;

-- 写出库流水，按均价计成本
INSERT INTO inventory_txn (txn_id, warehouse_id, product_id, txn_type, qty_in, qty_out, unit_cost, src_doc, src_line, txn_time)
VALUES (:txn_id, :wh, :prod, 'DELIVERY', 0, :qty, :avg_cost, :dn_id, :dn_line_id, NOW());

COMMIT;

毛利计算（订单行维度）：

SELECT sol.so_id, sol.product_id, dl.dn_line_id,
sol.qty AS order_qty, dl.qty AS delivered_qty,
sol.price AS sell_price, it.unit_cost AS cogs_unit,
dl.qty * sol.price - dl.qty * it.unit_cost AS gross_profit
FROM sales_order_line sol
JOIN delivery_line dl ON dl.product_id = sol.product_id AND dl.dn_id = (SELECT ref_so_id FROM delivery WHERE dn_id = dl.dn_id)
JOIN inventory_txn it ON it.src_line = dl.dn_line_id AND it.txn_type='DELIVERY';

十、总结与进一步建议

主要观点：
以库存流水为唯一记账源，配合余额快照，确保准确与性能。
成本核算选型清晰：加权平均更易维护，FIFO更精细；必要时双口径并存。
事务与并发控制不可忽略，负库存防护、幂等与审计必须到位。
用视图/ETL统一报表口径，索引与分区保障规模化性能。
行动步骤： 1）先搭建核心数据模型与命名规范；补齐主数据与审计字段。 2）实现“写流水→更新余额”的原子事务；选择成本方法并落地SQL。 3）为常用报表建立视图与快照机制；完成压力测试与极端场景校验。 4）逐步引入分区与归档，优化查询与ETL链路；上线监控告警。 5）若希望低成本快速落地，可直接使用成熟模板并按需自定义，如简道云进销存（官网地址： https://s.fanruan.com/xrxfy; ），在此基础上再补充企业的个性化SQL与规则。

最后推荐：分享一个我们公司在用的进销存系统模板，需要的可以自取，可直接使用，也可以自定义编辑修改：https://s.fanruan.com/xrxfy

精品问答:

进销存SQL的基本编写方法有哪些？

我刚开始学习进销存系统的SQL编写，发现有很多复杂的查询和数据处理操作，不太清楚基本的编写方法是什么，能否介绍一下进销存SQL的基础写法和注意事项？

进销存SQL的基本编写方法主要包括以下几个方面：

数据表设计合理：通常包括商品表、库存表、销售表和采购表，确保字段完整且关系明确。
使用标准的CRUD操作：增（INSERT）、删（DELETE）、改（UPDATE）、查（SELECT）是核心。
关联查询（JOIN）：通过INNER JOIN、LEFT JOIN连接商品、进货和销售数据，实现库存动态计算。
聚合函数应用：使用SUM、COUNT等函数统计销售数量、库存总量，提升数据分析效率。
过滤条件优化：合理使用WHERE子句避免全表扫描，提升查询性能。

例如，查询某商品当前库存的SQL语句：

SELECT p.product_name, (COALESCE(SUM(i.quantity),0) - COALESCE(SUM(s.quantity),0)) AS current_stock
FROM products p
LEFT JOIN inventory i ON p.product_id = i.product_id
LEFT JOIN sales s ON p.product_id = s.product_id
WHERE p.product_id = 1001
GROUP BY p.product_name;

该方法确保实时计算库存，适合进销存系统中的库存管理。

进销存SQL如何实现库存动态更新？

我在写进销存系统SQL时，想知道如何确保库存数据随着采购和销售实时更新，有哪些SQL技术或方法可以实现库存的动态管理？

库存动态更新是进销存SQL的关键环节，常见实现方法包括：

方法	说明	案例说明
触发器（Trigger）	自动在销售或采购表更新时调整库存表数据	当插入销售记录，触发器减少对应商品库存数量
存储过程（Procedure）	定义库存变动逻辑，调用存储过程进行更新	在订单确认时调用存储过程，批量更新库存
视图（View）	利用视图动态计算库存，无需物理更新库存表	创建视图实时计算“采购总量 - 销售总量”为库存

例如，使用触发器实现销售后库存减少：

CREATE TRIGGER trg_after_sale
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
   UPDATE inventory
   SET quantity = quantity - NEW.quantity
   WHERE product_id = NEW.product_id;
END;

该触发器确保每次销售新增后，库存数量自动减少，实现库存的动态更新。

进销存SQL中如何优化查询性能？

我发现我的进销存系统SQL查询速度很慢，尤其是涉及大量销售和库存数据时，想知道有哪些SQL优化技巧可以提高查询效率？

优化进销存SQL查询性能的关键措施包括：

索引优化：为常用的查询字段（如product_id、order_date）建立索引，提升检索速度。一般建立B-Tree索引，能将查询时间从O(n)降低到O(log n)。
查询简化：避免使用SELECT *，只查询必要字段，减少数据传输量。
分页查询：对大数据量使用LIMIT和OFFSET分页查询，避免一次加载大量数据。
预计算字段：对复杂统计结果预先计算并存储，减少实时计算压力。
使用EXPLAIN分析执行计划，定位慢查询。

以索引为例，创建product_id索引：

CREATE INDEX idx_product_id ON sales(product_id);

通过合理索引，查询速度可提升30%-70%，显著改善进销存系统的响应时间。

进销存SQL如何结合实际案例进行撰写？

我想通过实际案例理解进销存SQL的撰写方法，能否提供一个典型的进销存SQL案例，并详细说明每个步骤的作用？

结合实际案例撰写进销存SQL，便于理解和应用。以下为一个典型案例：

案例目标：查询某时间段内每个商品的销售数量和剩余库存

SQL示例：

SELECT p.product_name,
       COALESCE(SUM(s.quantity), 0) AS total_sales,
       (COALESCE(SUM(i.quantity),0) - COALESCE(SUM(s.quantity),0)) AS remaining_stock
FROM products p
LEFT JOIN inventory i ON p.product_id = i.product_id
LEFT JOIN sales s ON p.product_id = s.product_id AND s.sale_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY p.product_name;

步骤说明：

步骤	作用
SELECT字段	选择商品名称、销售总量和剩余库存作为输出字段
LEFT JOIN inventory	关联库存表获取进货数量
LEFT JOIN sales	关联销售表，限制时间范围统计销售数量
COALESCE函数	处理NULL值，确保计算准确
GROUP BY	按商品分组统计销售和库存数据

通过该案例，可以直观理解进销存SQL的结构和逻辑，便于实际开发中灵活运用。

简道云——国内领先的企业级零代码应用搭建平台

了解更多简道云官网

文章版权归" "www.jiandaoyun.com所有。
转载请注明出处：https://www.jiandaoyun.com/nblog/21741/
温馨提示：文章由AI大模型生成，如有侵权，联系 mumuerchuan@gmail.com 删除。