我试图这样做,但这对我来说是不可能的。 解决方案 我只需要将当前表的单列数据显示为多列。 例如列 [状态] 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)
可以使用条件聚合来透视数据集:
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