mysql - 将每一对行合并到一个 MYSQL 中

Question

我写了部分查询，但卡在了最后部分。到目前为止我拥有的代码:

SELECT uid, 
       x, 
       y 
FROM   (SELECT uid, 
               x, 
               y, 
               ( Timediff(Max(ts), Min(ts)) ) idle_time 
        FROM   test 
        WHERE  speed = 0 
        GROUP  BY x, 
                  y, 
                  uid 
        HAVING Count(*) > 0) t 
WHERE  idle_time > 1 
ORDER  BY uid 

结果表是:

----------------------------------
| uId | x        | y             |  
----------------------------------
| 1   | 110      | 20            |  <-- Uid = 1 start position
----------------------------------
| 1   | 220      | 50            |  <-- Uid = 1 end position
----------------------------------
| 2   | 30      | 15             |  <-- Uid = 2 start position
----------------------------------
| 2   | 40      | 22             |  <-- Uid = 2 end position
----------------------------------  

我现在需要得到的是:

------------------------------------------------------
| uId | x_start | x_end       | y _start    | y_end  |  
------------------------------------------------------
| 1   | 110      | 220        |  20         | 50     |
------------------------------------------------------
| 2   | 30      | 40         |  15          |  22    |
------------------------------------------------------

结果总是成对出现。这意味着可以有多个具有相同 uId 的行，只需添加对。我知道这并不困难，感谢任何帮助!!

编辑:

忘记提及，END 的 X 和 Y 并不总是大于 START，因为在实际数据库 map 坐标中使用。

Answer 1

没有一个很好的方法来做到这一点，因为你不能在 mySQL 上使用 CTE

首先，您需要返回内部查询的时间戳

------------------------------------------
| uId | x       | y   | ts               |
------------------------------------------
| 1   | 110     | 20  | 2017-11-23 15:01 |
------------------------------------------
| 1   | 220     | 50  | 2017-11-23 16:11 |
------------------------------------------
| 2   | 30      | 15  | 2017-11-24 05:20 |
------------------------------------------
| 2   | 40      | 22  | 2017-11-24 11:30 |
------------------------------------------

然后您可以编写以下查询:

为了简单起见，我将调用您的innerQuery，因为您必须重复该代码。

SELECT q1.uid as uid
       q1.x as x_start,
       q1.y as y_start,
       q2.x as x_end,
       q2.y as y_end
FROM ( innerQuery ) as q1
JOIN ( innerQuery ) as q2
  ON q1.uid = q2.uid
 AND q1.ts < q2.ts