mysql查询合并一张表上的2个选择

Question

如何像这样在一张表上组合 2 个选择？

表 T1:

id | status | ts
1  | 0      |2019-02-22 00:00:00
2  | 0      |2019-02-22 00:00:00
3  | 0      |2019-02-22 00:00:00
1  | 1      |2019-02-22 01:00:00
3  | 1      |2019-02-22 01:30:00
1  | 0      |2019-02-22 02:00:00
3  | 0      |2019-02-22 03:00:00

我想查找每个 ID 的 status = 0 和 status = 1 的最后值

1  | 0      |2019-02-22 02:00:00 with 1       | 1      |2019-02-22 01:00:00
2  | 0      |2019-02-22 00:00:00
3  | 0      |2019-02-22 03:00:00 with 3       | 1      |2019-02-22 01:30:00

我尝试过:

SELECT id, status, MAX(ts) 
 from T1 
where status = 1 Union SELECT id, status, MAX(ts) from T1 where status = 0

但不行...:o(

也许可以计算每个 ID 的 2 个状态变化之间的时间差...

Answer 1

条件聚合应该有技巧:

SELECT 
    id, 
    MAX(CASE WHEN status = 0 THEN ts END) ts_0, 
    MAX(CASE WHEN status = 1 THEN ts END) ts_1 
FROM T1
GROUP BY id

<强> Demo on DB Fiddle :

| id  | ts_0                | ts_1                |
| --- | ------------------- | ------------------- |
| 1   | 2019-02-22 02:00:00 | 2019-02-22 01:00:00 |
| 2   | 2019-02-22 00:00:00 |                     |
| 3   | 2019-02-22 03:00:00 | 2019-02-22 01:30:00 |