如何统计loans表quantity总和时扣除order_meta指定元数据的记录？

问题背景 #

需要统计指定日期范围内特定产品的每日贷款总quantity，当order_item_id在order_meta表中存在meta_key='returned'且meta_value='yes'的记录时，需从总量中扣除对应quantity。例如2024-01-01总quantity为2，但其中1条已归还，最终结果应为1。

测试数据 #

先定义测试用的表结构和数据：

orders表（主订单表） #

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    product_id INT
);
INSERT INTO orders VALUES
(1, '2024-01-01', 100),
(2, '2024-01-01', 100);

order_items表（订单商品表） #

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);
INSERT INTO order_items VALUES
(101, 1, 1),
(102, 2, 1);

order_meta表（订单元数据表） #

CREATE TABLE order_meta (
    meta_id INT PRIMARY KEY,
    order_item_id INT,
    meta_key VARCHAR(50),
    meta_value VARCHAR(50),
    FOREIGN KEY (order_item_id) REFERENCES order_items(order_item_id)
);
INSERT INTO order_meta VALUES
(201, 101, 'returned', 'yes');

常见错误原因 #

使用LEFT JOIN关联order_meta后出现分组语法错误，通常是因为：

• 分组时包含了order_meta表中未聚合的字段
• 未正确处理LEFT JOIN返回的NULL值，导致无法正确扣除已归还的quantity

正确SQL实现方案 #

方案一：CASE WHEN直接计算有效量 #

通过CASE WHEN判断每个订单项是否已归还，直接计算每日有效总量：

SELECT 
    o.order_date,
    SUM(
        CASE 
            WHEN om.order_item_id IS NOT NULL THEN 0
            ELSE oi.quantity
        END
    ) AS effective_quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN order_meta om 
    ON oi.order_item_id = om.order_item_id 
    AND om.meta_key = 'returned' 
    AND om.meta_value = 'yes'
WHERE o.product_id = 100 
    AND o.order_date BETWEEN '2024-01-01' AND '2024-01-01' -- 按需调整日期范围
GROUP BY o.order_date;

方案二：子查询预聚合已归还量 #

先通过子查询统计所有已归还的订单项数量，再与主查询关联计算差值：

WITH returned_quantities AS (
    SELECT 
        oi.order_item_id,
        oi.quantity AS returned_qty
    FROM order_items oi
    JOIN order_meta om 
        ON oi.order_item_id = om.order_item_id 
        AND om.meta_key = 'returned' 
        AND om.meta_value = 'yes'
)
SELECT 
    o.order_date,
    SUM(oi.quantity) - COALESCE(SUM(rq.returned_qty), 0) AS effective_quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN returned_quantities rq ON oi.order_item_id = rq.order_item_id
WHERE o.product_id = 100 
    AND o.order_date BETWEEN '2024-01-01' AND '2024-01-01'
GROUP BY o.order_date;

两个方案执行后，2024-01-01的effective_quantity都会返回1，符合需求。

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