mysql - 如何将行值放入新列中以提高可读性？

Question

我有一个 MySQL 表，其中同一时间段存在单独的行，每个位置一行:

+---------------------+----------+-------+-------------+
| time                | location | state | probability |
+---------------------+----------+-------+-------------+
| 2019-08-08 22:04:11 |        A |     0 |        0    |
| 2019-08-08 22:04:11 |        B |     0 |        0.2  |
| 2019-08-08 22:04:11 |        C |     1 |        0.48 |
| 2019-08-08 22:04:21 |        A |     0 |        0.18 |
| 2019-08-08 22:04:21 |        B |     0 |        0.22 |
| 2019-08-08 22:04:21 |        C |     1 |        0.92 |

如果这些列看起来更像:

| time                | A_st | A_pb | B_st | B_pb | C_st | C_pb | 
| 2019-08-08 22:04:11 | 0    | 0    | 0    | 0.2  | 1    | 0.48 |
| 2019-08-08 22:04:21 | 0    | 0    | 0    | 0    | 1    | 0.92 | 

具有相同时间的多行变成单行，并且它们的值被插入到该时间的适当列中。

Answer 1

您可以使用条件聚合:

select time,
       max(case when location = 'A' then state end) as state_a,
       max(case when location = 'A' then probability end) as prob_a,
       max(case when location = 'B' then state end) as state_b,
       max(case when location = 'B' then probability end) as prob_b,
       max(case when location = 'C' then state end) as state_c,
       max(case when location = 'C' then probability end) as prob_c
from t
group by time;