MySQL'group by'与'left join'抛出一些条目 [英] MySQL 'group by' with 'left join' throws away some entries

问题描述

 选择
 orders.order_id，
 items.item_id，
 GROUP_CONCAT（graphics.graphic_id）AS graphic_ids 
 FROM订单
 LEFT JOIN order_items items ON orders.order_id = items.order_id 
 LEFT JOIN图形上的order_graphics图形。 item_id = items.item_id 
 WHERE // etc 
 GROUP BY items.item_id 
  

订单可以有零个或多个项目，项目可以有零个或多个图形。我们的目标是获得所有与WHERE条件匹配的订单，按订单商品列出它们，并列出每个订单商品上的图形，例如： c $ c> //理想
+ ---------- + --------- + --------------- +
| order_id | item_id | graphic_ids |
+ ---------- + --------- + --------------- +
| 3097 | NULL | NULL |
| 3098 | NULL | NULL |
| 3099 | NULL | NULL |
| 3100 | 112437 | NULL |
| 3101 | 112677 | 127003 |
| 3102 | 112948 | 127314 |
| 3102 | 112949 | 127315 |
| 3102 | 112950 | 127316 |
| 3103 | 122807 | 138005,138006 |
+ ---------- + --------- + --------------- +

$ p
$ b

然而，GROUP BY / GROUP_CONCAT子句意味着所有包含零项的订单都归入NULL下：

  //现实
 + ---------- + --------- + ------ --------- + 
 | 3097 | NULL | NULL | 
 | 3100 | 112437 | NULL | 
 | 3101 | 112677 | 127003 | 
 | 3102 | 112948 | 127314 | 
 | 3102 | 112949 | 127315 | 
 | 3102 | 112950 | 127316 | 
 | 3103 | 122807 | 138005,138006 | 
 + ---------- + --------- + --------------- + 
  

有没有一种很好的方法来 group by `item_id，同时仍列出所有订单没有任何项目？

解决方案

您应该让每一列不是您的 group by 子句：

  SELECT 
 orders.order_id，
 items .item_id，
 GROUP_CONCAT（graphics.graphic_id）AS graphic_ids 
 FROM命令命令
 LEFT JOIN order_items项目ON orders.order_id = items.order_id 
 LEFT JOIN graphics.item_id上的order_graphics图形= items.item_id 
 WHERE // etc 
 GROUP BY orders.order_id，items.item_id 
  

I have a query that looks something like this:

SELECT 
    orders.order_id,
    items.item_id, 
    GROUP_CONCAT(graphics.graphic_id) AS graphic_ids
FROM orders orders 
    LEFT JOIN order_items items ON orders.order_id = items.order_id 
    LEFT JOIN order_graphics graphics on graphics.item_id = items.item_id
WHERE // etc
GROUP BY items.item_id

Orders can have zero or more items, and items can have zero or more graphics. The goal is to get all orders matching the WHERE condition, itemize them by order item, and list the graphics on each order item, like so:

// ideal
+----------+---------+---------------+    
| order_id | item_id | graphic_ids   |
+----------+---------+---------------+
|     3097 |  NULL   | NULL          |
|     3098 |  NULL   | NULL          |
|     3099 |  NULL   | NULL          |
|     3100 |  112437 | NULL          |
|     3101 |  112677 | 127003        |
|     3102 |  112948 | 127314        |
|     3102 |  112949 | 127315        |
|     3102 |  112950 | 127316        |
|     3103 |  122807 | 138005,138006 |
+----------+---------+---------------+

However, the GROUP BY / GROUP_CONCAT clauses mean that all orders with zero items are grouped under NULL:

// reality
+----------+---------+---------------+
|     3097 |  NULL   | NULL          |
|     3100 |  112437 | NULL          |
|     3101 |  112677 | 127003        |
|     3102 |  112948 | 127314        |
|     3102 |  112949 | 127315        |
|     3102 |  112950 | 127316        |
|     3103 |  122807 | 138005,138006 |
+----------+---------+---------------+

Is there a good way to group by `item_id while still listing out all orders with no items on them?

解决方案

You should have every column that isn't an aggregate calculation in your group by clause:

SELECT 
    orders.order_id,
    items.item_id, 
    GROUP_CONCAT(graphics.graphic_id) AS graphic_ids
FROM orders orders 
    LEFT JOIN order_items items ON orders.order_id = items.order_id 
    LEFT JOIN order_graphics graphics on graphics.item_id = items.item_id
WHERE // etc
GROUP BY orders.order_id, items.item_id

这篇关于MySQL'group by'与'left join'抛出一些条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！