mysql – 自联接只返回一条记录

在库存管理系统上工作,我们有以下表格：

================================================
| 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很难阅读.)

mysql – 自联接只返回一条记录

猜你在找的MySQL相关文章