php - 多日期范围股票超售查询

Question

我一直在为一个查询而苦苦挣扎，我不确定它是否可以用纯 sql 或需要由 php 支持。

基本上我想生成一份订单报告，列出系统中超额预订的产品。

表结构:

订单

id int
bookingStart DATETIME
bookingEnd DATETIME

订单行

id int
qty int
product_id int
booking_id int

产品

id int
stock int

一个订单可以包含多行相同的产品。因此，订单上的每个产品都需要有一个 SUM。有没有人对实现此目标的最佳方法有任何建议？

我已经尝试使用子查询在特定时间间隔内循环，但它没有考虑不同预订之间的重叠，如果可以通过单个查询完成，我有点卡在这里:

SELECT  (SELECT SUM(lin2.qty) FROM booking_lines lin2,orders b2
                       WHERE
                         lin2.product_id=e.id    AND
                       b2.id=lin2.booking_id  AND                           (
                       (b2.bookingStart BETWEEN b1.bookingStart AND b1.bookingEnd) OR
                       (b2.bookingEnd BETWEEN b1.bookingStart AND b1.bookingEnd) OR
                       (b2.bookingStart < b1.bookingStart AND b2.bookingEnd >     b1.bookingEnd)                           
                       )    as booked SUM(lin1.qty) 
                 FROM orders b1
                 LEFT JOIN order_lines lin1 ON b1.id=lin1.booking_id
                 LEFT JOIN products e ON e.id=lin1.product_id

                (
                    (b1.bookingStart BETWEEN '$s' AND '$e') OR
                    (b1.bookingEnd BETWEEN '$s' AND '$e') OR
                    (b1.bookingStart < '$s' AND b1.bookingEnd > '$e') 
        )
         GROUP BY b1.id,lin1.product_id

问题如果我们的产品 x 的库存为 10。

订单1已经预订了3件商品x订单 2 已预订 5 件商品 x订单3已预订8件商品x

下面几行表示预定的时间跨度

______订购 1 x:3 em>____
___订购 2 x:5_____ __________订购 3 x:8 em>______
__订购 4 x:2_

所以只有当 3 个订单重叠时，产品 x 才实际上超额预订，我不知道如何创建能够检测到这一点的查询。

Answer 1

表结构

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(255),
    stock      INT
) ENGINE=InnoDB;

CREATE TABLE orders (
    order_id     INT AUTO_INCREMENT PRIMARY KEY,
    bookingStart DATETIME,
    bookingEnd   DATETIME
) ENGINE=InnoDB;


CREATE TABLE order_lines (
    line_id       INT AUTO_INCREMENT PRIMARY KEY,
    order_id      INT,
    product_id    INT,
    qty           INT NOT NULL DEFAULT 0,
    CONSTRAINT FOREIGN KEY(order_id)   REFERENCES orders(order_id) ON DELETE RESTRICT,
    CONSTRAINT FOREIGN KEY(product_id) REFERENCES products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB;


INSERT INTO products(name, stock) VALUES('Product 1', 8);
INSERT INTO products(name, stock) VALUES('Product 2', 14);
INSERT INTO products(name, stock) VALUES('Product 3', 25);

INSERT INTO orders(bookingStart,bookingEnd) VALUES(NOW(), (NOW() + INTERVAL 2 HOUR));
INSERT INTO orders(bookingStart,bookingEnd) VALUES(NOW(), (NOW() + INTERVAL 3 HOUR));
INSERT INTO orders(bookingStart,bookingEnd) VALUES(NOW(), (NOW() + INTERVAL 4 HOUR));

INSERT INTO order_lines(order_id, product_id, qty) VALUES(1, 1, 2);
INSERT INTO order_lines(order_id, product_id, qty) VALUES(1, 1, 8);
INSERT INTO order_lines(order_id, product_id, qty) VALUES(2, 2, 5);
INSERT INTO order_lines(order_id, product_id, qty) VALUES(2, 2, 10);
INSERT INTO order_lines(order_id, product_id, qty) VALUES(3, 3, 2);
INSERT INTO order_lines(order_id, product_id, qty) VALUES(3, 3, 8);
INSERT INTO order_lines(order_id, product_id, qty) VALUES(3, 3, 10);

获取超售产品:

SELECT
  products.*,
  SUM(order_lines.qty) as sum_qty
FROM products
LEFT JOIN order_lines ON order_lines.product_id = products.product_id
LEFT JOIN orders ON orders.order_id = order_lines.order_id
WHERE orders.bookingStart >= '2013-06-12' 
AND orders.bookingEnd <= '2013-06-14'
GROUP BY order_lines.order_id, order_lines.product_id
HAVING sum_qty > products.stock

将返回Product 1和Product 2，因为order_lines中qty的总和大于products.stock(大概是产品的可用数量？)。

SqlFiddle