在库存管理系统上工作,我们有以下表格:
================================================
| orders | order_line_items | product_options |
|--------|-------------------|-----------------|
| id | id | id |
| start | order_id | name |
| end | product_option_id | |
| | quantity | |
| | price | |
| | event_start | |
| | event_end | |
================================================
我正在尝试计算特定日期的库存,所以我需要进行自我连接,将order_line_items上的数量与order_line_items中具有相同product_option_id的其他记录数量的SUM进行比较,以及事件开始和结束的位置是在一定范围内.
那么,鉴于2016-01-20的日期,我有:
SELECT order_line_items.id,order_line_items.product_option_id,order_line_items.order_id FROM order_line_items
WHERE order_line_items.event_end_date >= '2016-01-20 04:00:00'
AND order_line_items.event_start_date <= '2016-01-21 04:00:00'
AND order_line_items.product_option_id IS NOT NULL;
以上返回127行
当我尝试自我加入时,如下:
SELECT
order_line_items.id,order_line_items.order_id,order_line_items.quantity,other_line_items.other_product_option_id,other_line_items.other_order_id,other_line_items.other_quantity,other_line_items.total
FROM order_line_items
JOIN (
SELECT
id,product_option_id AS other_product_option_id,order_id AS other_order_id,quantity AS other_quantity,SUM(quantity) total
FROM order_line_items
WHERE order_line_items.event_end_date >= '2016-01-20 04:00:00'
AND order_line_items.event_start_date <= '2016-01-21 04:00:00'
) other_line_items ON order_line_items.product_option_id = other_line_items.other_product_option_id
WHERE order_line_items.event_end_date >= '2016-01-20 04:00:00'
AND order_line_items.event_start_date <= '2016-01-21 04:00:00'
AND order_line_items.product_option_id IS NOT NULL;
它只返回1条记录.正如你在这里看到的那样:(https://goo.gl/BhUYxK)有很多记录使用相同的product_option_id,所以最后一个查询应该返回很多行
最佳答案
添加的SUM(…)将子查询转换为单行.也许子查询需要其中一个:
GROUP BY (id)
GROUP BY (product_option_id)
GROUP BY (order_id)
(我不清楚架构或应用程序是否足以说出哪些有意义.)
(请使用更短,更独特的别名;由于order_line_items和other_line_items的长度和相似性,SQL很难阅读.)