如何通过SQL关联ITEM、STOCK、COUNT表生成指定REPORT报表？

1. 创建REPORT表结构 #

先创建匹配需求的报表存储表：

CREATE TABLE REPORT (
    item_id INT PRIMARY KEY, -- 类型可根据实际业务调整，比如VARCHAR
    name VARCHAR(100) NOT NULL,
    stock_quantity INT DEFAULT 0,
    total_counting_quantity INT DEFAULT 0
);

2. 生成报表数据 #

通过关联三张表，筛选1号仓库库存并汇总盘点数量，将结果插入REPORT表：

INSERT INTO REPORT (item_id, name, stock_quantity, total_counting_quantity)
SELECT 
    i.item_id,
    i.name,
    COALESCE(s.stock_quantity, 0) AS stock_quantity,
    COALESCE(SUM(c.counting_quantity), 0) AS total_counting_quantity
FROM ITEM i
LEFT JOIN STOCK s 
    ON i.item_id = s.item_id 
    AND s.location_id = '1' -- 仅匹配1号仓库的库存数据
LEFT JOIN COUNT c 
    ON i.item_id = c.item_id
GROUP BY i.item_id, i.name, s.stock_quantity;

• 用LEFT JOIN确保所有商品都能出现在报表中，即使该商品无库存或未被盘点
• COALESCE将空值转为0，避免报表出现NULL

3. 配套业务表创建 #

3.1 COUNT表（扫码盘点数据存储） #

用于存储扫码枪录入的盘点记录：

CREATE TABLE COUNT (
    item_id INT NOT NULL,
    counting_quantity INT NOT NULL,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, -- 自动记录录入时间
    FOREIGN KEY (item_id) REFERENCES ITEM(item_id),
    INDEX idx_item_id (item_id) -- 优化关联查询效率
);

3.2 库存差值监控表 #

实时记录盘点时库存与扫码数量的差异：

CREATE TABLE STOCK_COUNT_DIFF (
    diff_id INT AUTO_INCREMENT PRIMARY KEY,
    item_id INT NOT NULL,
    stock_quantity INT NOT NULL,
    counting_quantity INT NOT NULL,
    diff INT NOT NULL, -- 差值：stock_quantity - counting_quantity
    record_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (item_id) REFERENCES ITEM(item_id)
);

如果需要实时插入差异记录，可通过触发器实现：

DELIMITER //
CREATE TRIGGER after_count_insert
AFTER INSERT ON COUNT
FOR EACH ROW
BEGIN
    DECLARE stock_qty INT;
    SELECT stock_quantity INTO stock_qty 
    FROM STOCK 
    WHERE item_id = NEW.item_id AND location_id = '1';
    
    INSERT INTO STOCK_COUNT_DIFF (item_id, stock_quantity, counting_quantity, diff)
    VALUES (NEW.item_id, COALESCE(stock_qty, 0), NEW.counting_quantity, COALESCE(stock_qty, 0) - NEW.counting_quantity);
END //
DELIMITER ;

4. 盘点后收尾操作 #

导出REPORT表数据后，清空COUNT表以备下月盘点：

TRUNCATE TABLE COUNT;

TRUNCATE比DELETE更高效，适合批量清空表的场景，同时会重置表的自增序列（如果有）。

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