mysql - 将两行合并为一行添加唯一数据

Question

我在 MYSQL 数据库中的每个订单都有两行，除了以下内容之外，它们都具有所有相同的信息:

pickup-location-id

我想知道是否有一种方法可以在连接时将行合并为一个

pickup-location-id for EX row1

pickup-location-id2 for EX row2

进入ONE ROW，订单 ID 用于两者。

这是我最接近的......

SELECT *
    FROM `t_order-transportation`
    INNER JOIN (SELECT `pickup-location` AS `pickup-location-2`,
                `order-id`
               FROM `t_order-transportation`
               GROUP  BY `pickup-location`
               HAVING COUNT(id) > 1) dup
           ON `t_order-transportation`.`order-id` = dup.`order-id`

这几乎是我想要的，但它似乎显示 4 行，2 行用于原始行，2 行用于新行，两种方式都连接。

Sample of select return || RED IS ORIGINAL ROWS

MySQL fiddle :http://sqlfiddle.com/#!9/4b441/1

Answer 1

我建议一个简单的内部连接

SELECT a.*, b.*
FROM `t_order-transportation` as a
INNER JOIN `t_order-transportation` as b 
              on ( a.`order-id` = b.`order-id` 
                 and a.`pickup-datetime` < b.`pickup-datetime`)

你可以去的地方

SELECT a.*, b.`pickup-location`
FROM `t_order-transportation` as a
INNER JOIN `t_order-transportation` as b 
              on (a.`order-id` = b.`order-id`
               and a.`pickup-datetime` < b.`pickup-datetime`)