mysql - 是否可以在 MySQL 中聚合这样的行？

Question

有了下表的车型，

+-------+----+-------+
| model | id | total |
+-------+----+-------+
| civic |  1 |   27  |
| civic |  3 |   11  |
| crv   |  3 |    5  |
+-------+----+-------+

是否可以查询得到这个结果？

+-------+-----+-----+-----+
| model | id1 | id2 | id3 |
+-------+-----+-----+-----+
| civic |  27 |   0 |  11 |
| crv   |   0 |   0 |   5 |
+-------+-----+-----+-----+

Answer 1

这是一个数据透视查询。在 MySQL 中处理此问题的一种方法是条件聚合:

select model,
       max(case when id = 1 then total else 0 end) as id1,
       max(case when id = 2 then total else 0 end) as id2,
       max(case when id = 3 then total else 0 end) as id3
from carmodels t
group by model;