mysql - 在 JOIN 查询中将列设置为 null 或为空

Question

订单表

ORDER_ID|ORDER_DT
1       |12-12-2016

产品表

PRODUCT ID | ORDER ID
1          | 1
2          | 1

使用SELECT * FROM order o LEFT JOIN Product p ON o.order_id = p.order_id;

结果

 PRODUCT ID | ORDER ID|ORDER_DT
 1          | 1       |12-12-2016
 2          | 1       |12-12-2016
 3          | 1       |12-12-2016

但我需要这样的结果，注意到我仍然有相同的编号。订单表列中重复的行已被清空。

PRODUCT ID | ORDER ID|ORDER_DT
     1     | 1       |12-12-2016
     2     |         |
     3     |         |

Answer 1

您可以使用临时变量来做到这一点，例如:

SELECT product_id, orders.order_id, order_dt, IF(@previous = orders.order_id, 0, orders.order_id) AS oid, @previous:= orders.order_id
FROM orders
LEFT JOIN products
ON orders.order_id = products.order_id,
(SELECT @previous := -1) a;

并将其包装到另一个 SELECT 查询中以获取所需的列，例如:

SELECT r.product_id, IF(r.oid = 0, '', r.oid) AS order_id, r.order_dt 
FROM (
    SELECT product_id, orders.order_id, order_dt, IF(@previous = orders.order_id, 0, orders.order_id) AS oid, @previous:= orders.order_id
    FROM orders
    LEFT JOIN products
    ON orders.order_id = products.order_id,
    (SELECT @previous := -1) a
    ) r;

这是SQL Fiddle 。