基于条件的库存交易记录汇总查询实现方案问询

1. SQL查询实现 #

假设你的表结构如下：

• temporaryTable：idItem, previousDate, latestDate（存储用户指定的物品ID及查询日期区间）
• tableTransaction：idItem, transactionDate, qty（qty为正代表入库，负代表出库；若用transactionType字段区分出入库，会补充对应写法）

方式一：基于交易明细直接计算 #

SELECT
    t.idItem,
    -- 期初余额：previousDate当天或之前最后一笔交易的库存
    (SELECT TOP 1 lastQty FROM tableTransaction 
     WHERE idItem = t.idItem AND transactionDate <= t.previousDate
     ORDER BY transactionDate DESC) AS beginningBalance,
    -- 期末余额：latestDate当天或之前最后一笔交易的库存
    (SELECT TOP 1 lastQty FROM tableTransaction 
     WHERE idItem = t.idItem AND transactionDate <= t.latestDate
     ORDER BY transactionDate DESC) AS endingBalance,
    -- 总入库：日期区间内所有正数量之和
    COALESCE(SUM(CASE WHEN tt.qty > 0 THEN tt.qty ELSE 0 END), 0) AS totalInput,
    -- 总出库：日期区间内所有负数量绝对值之和
    COALESCE(SUM(CASE WHEN tt.qty < 0 THEN ABS(tt.qty) ELSE 0 END), 0) AS totalOutput
FROM temporaryTable t
LEFT JOIN tableTransaction tt 
    ON tt.idItem = t.idItem 
    AND tt.transactionDate BETWEEN t.previousDate AND t.latestDate
GROUP BY t.idItem, t.previousDate, t.latestDate;

如果tableTransaction用transactionType（比如'IN'/'OUT'）区分出入库，将出入库统计部分替换为：

COALESCE(SUM(CASE WHEN tt.transactionType = 'IN' THEN tt.qty ELSE 0 END), 0) AS totalInput,
COALESCE(SUM(CASE WHEN tt.transactionType = 'OUT' THEN tt.qty ELSE 0 END), 0) AS totalOutput

方式二：用窗口函数优化性能 #

若交易数据量较大，窗口函数可减少子查询的重复扫描：

WITH ItemTransactions AS (
    SELECT
        tt.idItem,
        tt.transactionDate,
        tt.qty,
        -- 累计计算库存余额
        SUM(tt.qty) OVER (PARTITION BY tt.idItem ORDER BY tt.transactionDate) AS runningBalance,
        t.previousDate,
        t.latestDate
    FROM tableTransaction tt
    JOIN temporaryTable t ON tt.idItem = t.idItem
),
BalanceSnapshots AS (
    SELECT
        idItem,
        previousDate,
        latestDate,
        -- 期初余额：previousDate前最后一个余额
        FIRST_VALUE(runningBalance) OVER (PARTITION BY idItem ORDER BY 
            CASE WHEN transactionDate <= previousDate THEN 0 ELSE 1 END, transactionDate DESC) AS beginningBalance,
        -- 期末余额：latestDate前最后一个余额
        FIRST_VALUE(runningBalance) OVER (PARTITION BY idItem ORDER BY 
            CASE WHEN transactionDate <= latestDate THEN 0 ELSE 1 END, transactionDate DESC) AS endingBalance,
        SUM(CASE WHEN qty > 0 THEN qty ELSE 0 END) OVER (PARTITION BY idItem) AS totalInput,
        SUM(CASE WHEN qty < 0 THEN ABS(qty) ELSE 0 END) OVER (PARTITION BY idItem) AS totalOutput
    FROM ItemTransactions
)
SELECT DISTINCT
    idItem,
    beginningBalance,
    endingBalance,
    totalInput,
    totalOutput
FROM BalanceSnapshots;

2. 数据库设计优化建议 #

• 添加复合索引：给tableTransaction创建(idItem, transactionDate)的复合索引，大幅提升按物品+日期范围查询的速度，数据量较大时效果尤为明显。
• 引入库存快照表：若频繁查询历史库存余额，可定期（比如每日凌晨）生成inventory_snapshot表，存储idItem, snapshotDate, balance。查询时直接取快照数据，避免每次遍历所有历史交易，性能提升显著。
• 明确字段语义：若用qty正负区分出入库，建议在字段注释里明确规则；若用transactionType，建议设置为枚举类型（比如MySQL的ENUM('IN','OUT')），避免非法值。
• 标准化日期格式：确保transactionDate、previousDate、latestDate统一为DATE或DATETIME类型，避免字符串类型导致的查询错误和性能问题。

内容的提问来源于stack exchange，提问作者Binyamin W.