Android平台SQLite数据库中两张表关联计算剩余库存的实现方法

核心逻辑说明 #

先按商品编码分组查询出每个商品的最新销售记录，再关联库存表执行扣减操作，以下是具体实现步骤：

步骤1：查询每个商品的最新销售记录 #

通过子查询匹配每个商品的最大销售时间，拿到对应的最新销售数据：

SELECT s1.code, s1.name, s1.sell_qty
FROM sell_table s1
INNER JOIN (
    SELECT code, MAX(date) AS latest_date
    FROM sell_table
    GROUP BY code
) s2 ON s1.code = s2.code AND s1.date = s2.latest_date

注意：销售时间date字段需要用可排序的格式存储，比如yyyy-MM-dd HH:mm:ss格式字符串或者时间戳，否则MAX函数无法正确拿到最新记录。

步骤2：更新库存表扣减对应销售数量 #

适配Android 11+（SQLite 3.33.0及以上版本） #

高版本SQLite支持UPDATE FROM语法，可以直接关联子查询批量更新：

UPDATE stock_table
SET `total stock` = `total stock` - latest_sell.sell_qty
FROM (
    SELECT s1.code, s1.sell_qty
    FROM sell_table s1
    INNER JOIN (
        SELECT code, MAX(date) AS latest_date
        FROM sell_table
        GROUP BY code
    ) s2 ON s1.code = s2.code AND s1.date = s2.latest_date
) latest_sell
WHERE stock_table.code = latest_sell.code

注意：库存表的total stock字段带空格，需要用反引号包裹避免语法报错。

兼容低版本Android（SQLite 3.33.0以下版本） #

用嵌套子查询实现更新，适配所有Android系统版本：

UPDATE stock_table
SET `total stock` = `total stock` - (
    SELECT sell_qty
    FROM sell_table s1
    WHERE s1.code = stock_table.code
    ORDER BY date DESC
    LIMIT 1
)
WHERE EXISTS (
    SELECT 1 FROM sell_table s2 WHERE s2.code = stock_table.code
)

Android端代码示例 #

建议开启事务执行更新，避免异常导致数据不一致：

SQLiteDatabase db = dbHelper.getWritableDatabase();
db.beginTransaction(); // 开启事务
try {
    // 执行兼容全版本的更新SQL
    String updateSql = "UPDATE stock_table SET `total stock` = `total stock` - (SELECT sell_qty FROM sell_table s1 WHERE s1.code = stock_table.code ORDER BY date DESC LIMIT 1) WHERE EXISTS (SELECT 1 FROM sell_table s2 WHERE s2.code = stock_table.code)";
    db.execSQL(updateSql);
    db.setTransactionSuccessful(); // 标记事务执行成功
} catch (Exception e) {
    e.printStackTrace();
} finally {
    db.endTransaction(); // 未标记成功则自动回滚
    db.close();
}

额外注意事项 #

• 如果需要避免出现负数库存，可以在UPDATE语句的WHERE条件中追加AND total stock >= (SELECT sell_qty FROM sell_table s1 WHERE s1.code = stock_table.code ORDER BY date DESC LIMIT 1)，库存不足时不会执行扣减
• 要避免同一条销售记录被重复扣减的话，可以给sell_table新增is_deducted整型字段，扣减完成后更新对应记录的is_deducted为1，后续查询时过滤掉已扣减的记录即可

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