如何在Derby SQL中插入/更新关联表：实现商品数量汇总

表结构回顾 #

你的两张表创建语句如下：

// 创建ItemTable
createItemTable = "CREATE TABLE ItemTable "
        + "(ID INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
        + "ItemCode INTEGER, "
        + "Quantity INTEGER "
        + ")";

// 创建TotalQuantity表
createTotalQuantityTable = "CREATE TABLE TOTALQUANTITY " +
        "(ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY," +
        "TOTALQUANTITY INTEGER," +
        "ITEMID INTEGER NOT NULL REFERENCES ITEMTABLE (ID)," +
        "PRIMARY KEY (ID))";

插入总数量的SQL语句 #

要实现将ItemTable中相同ItemCode的商品总数量插入TotalQuantity表，可使用INSERT ... SELECT语法直接通过分组统计结果完成插入：

INSERT INTO TOTALQUANTITY (TOTALQUANTITY, ITEMID)
SELECT 
    SUM(Quantity) AS total_qty,
    MIN(ID) AS item_id
FROM ItemTable
GROUP BY ItemCode;

逻辑说明 #

• GROUP BY ItemCode：按商品编码分组，聚合相同编码的所有库存记录
• SUM(Quantity)：计算每组商品的总库存数量
• MIN(ID)：取每组中最小的ID作为关联的ITEMID（若业务中同一个ItemCode对应唯一ID，可直接替换为ID，但需确保分组后每个组仅返回一个ID）

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