mysql - 无论如何，是否可以将单列数据显示为多列

Question

我尝试过，但对我来说这是不可能的。我需要将单列数据显示为当前表的多列的解决方案。例如列[status] 3条记录{present,late,absent}显示3列单行，防止3行

SELECT tid,status, COUNT(status) AS counter FROM `attend_teacher` GROUP BY status,tid ORDER by tid

+-----+---------+---------+
| tid | status  | counter |
+-----+---------+---------+
|   1 | absent  |       1 |
|   1 | present |       2 |
|   1 | late    |       1 |
|   3 | late    |       1 |
+-----+---------+---------+

我想通过对tid进行分组来显示结果

+-----+---------+---------+--------+
| tid | Present | late    | absent |
+-----+---------+---------+--------+
|   1 |    2    |    1    |    1   |
+-----+---------+---------+--------+
|   3 |    0    |    1    |    0   |
+-----+---------+---------+--------+

表格结构

SELECT * FROM `attend_teacher`;
+----+-----+----------+---------+---------+----------------+
| id | tid | day      | status  | subject | date_created   |
+----+-----+----------+---------+---------+----------------+
|  1 |   1 | Saturday | present | 1,5     | 8 May 2019     |
|  2 |   1 | Saturday | present | 1,1,5,2 | 8 October 2019 |
|  3 |   1 | Saturday | absent  | 1,1,5,2 | 9 October 2019 |
|  4 |   1 | Saturday | late    | 1,1,5,2 | 9 October 2019 |
|  5 |   3 | Saturday | late    | 1,3,5,4 | 9 October 2019 |
+----+-----+----------+---------+---------+----------------+
5 rows in set (0.00 sec)

Answer 1

您可以使用条件聚合来透视数据集:

select
    tid,
    sum(case when status = 'present' then 1 else 0 end) present,
    sum(case when status = 'late' then 1 else 0 end) late,
    sum(case when status = 'absent ' then 1 else 0 end) absent 
from attend_teacher
group by tid