Firebird 3查询修改：获取商品最后进货价及库存销量数据

需求说明 #

• 基于Firebird 3数据库，调整现有库存计算SQL
• 库存计算公式：rest = 2022-01-01起始库存 + 2022-01-01至2023-08-15进货量 - 同期销量
• 替换原SQL中dt1派生表的求和逻辑，改为获取商品最后一次进货价格（last_income_price）
• 输出字段：goods_id、goods、last_income_price、sold_qnt（区间销量）、rest（2023-08-15库存）

数据库表结构 #

• goods：goods_id、goods...
• incomes：income_id、income_dt、qnt、income_price、goods_id...
• sales：sale_id、income_id、sale_dt、qnt、goods_id...

修改后的SQL代码 #

SELECT
    g.goods_id,
    g.goods,
    COALESCE(li.last_income_price, 0) AS last_income_price,
    COALESCE(s_period.sold_qnt, 0) AS sold_qnt,
    COALESCE(
        -- 起始库存（2022-01-01前进货总量 - 同期销量总量）
        (COALESCE(i_start.total_qnt, 0) - COALESCE(s_start.total_qnt, 0))
        + COALESCE(i_period.total_qnt, 0)  -- 区间进货量
        - COALESCE(s_period.sold_qnt, 0), -- 区间销量
        0
    ) AS rest
FROM goods g
-- 获取截止2023-08-15的最后一次进货价格
LEFT JOIN (
    SELECT 
        goods_id,
        income_price AS last_income_price
    FROM incomes i
    WHERE income_dt = (
        SELECT MAX(income_dt) 
        FROM incomes 
        WHERE goods_id = i.goods_id AND income_dt <= '2023-08-15'
    )
    GROUP BY goods_id, income_price
) li ON g.goods_id = li.goods_id
-- 计算2022-01-01前的进货总量
LEFT JOIN (
    SELECT goods_id, SUM(qnt) AS total_qnt
    FROM incomes
    WHERE income_dt < '2022-01-01'
    GROUP BY goods_id
) i_start ON g.goods_id = i_start.goods_id
-- 计算2022-01-01前的销量总量
LEFT JOIN (
    SELECT goods_id, SUM(qnt) AS total_qnt
    FROM sales
    WHERE sale_dt < '2022-01-01'
    GROUP BY goods_id
) s_start ON g.goods_id = s_start.goods_id
-- 计算区间内的进货总量
LEFT JOIN (
    SELECT goods_id, SUM(qnt) AS total_qnt
    FROM incomes
    WHERE income_dt BETWEEN '2022-01-01' AND '2023-08-15'
    GROUP BY goods_id
) i_period ON g.goods_id = i_period.goods_id
-- 计算区间内的销量
LEFT JOIN (
    SELECT goods_id, SUM(qnt) AS sold_qnt
    FROM sales
    WHERE sale_dt BETWEEN '2022-01-01' AND '2023-08-15'
    GROUP BY goods_id
) s_period ON g.goods_id = s_period.goods_id
ORDER BY g.goods_id;

关键调整说明 #

1. 最后进货价获取：通过子查询li定位每个商品截止到2023-08-15的最新进货记录，提取对应价格，替代原dt1的求和逻辑。
2. 库存逻辑拆分：将原合并计算拆分为4个独立子查询，分别计算起始日期前后的进货、销量数据，再按公式组合得到最终库存，逻辑更直观。
3. 字段精简：移除原SQL中冗余的startqnt、income_qnt等字段，仅保留需求指定输出列。
4. 日期固化：直接使用需求中的日期常量，如需复用可改为参数:d1、:d2形式。

内容的提问来源于stack exchange，提问作者basti