mysql - 如何将行作为标题然后计算填充的数据

Question

我正在尝试计算所有状态为已 checkout 的行，但我只能做的是按行填充它然后动态计数。

如何将我的行作为标题，然后计算之后的状态？

我需要将宿舍名称行作为列标题然后按行 checkout 。

查询:

SELECT  Room_Number as 'Room Number',Dorm_Name as 'Dorm Name',
 COUNT(IF(action = 'Checked Out' , 1, NULL)) 'Checked Out'  FROM billeting_history group by dorm_name;


+-----------+-------------+-------------+ 
| Dorm Name | Room_number | Checked Out |
+-----------+-------------+-------------+ 
| Arquitola |         205 |           1 |
| Hangar    |         201 |           0 |
| Noble     |         200 |           0 |
+-----------+-------------+-------------+ 

Desired output :

+-------------+-----------+--------+-------+ 
| Room Number | Arquitola | Hangar | Noble |
+-------------+-----------+--------+-------+
|         205 |         1 |      0 |     0 |
|         201 |         0 |      0 |     0 |
|         200 |         0 |      0 |     0 |
+-------------+-----------+--------+-------+

Answer 1

如果您知道所需的特定列，则可以使用条件聚合:

SELECT Room_Number, 
       SUM( CASE WHEN Dorm_Name = 'Arquitola' THEN checked_out ELSE 0 END ) as Arquitola,
       SUM( CASE WHEN Dorm_Name = 'Hangar' THEN checked_out ELSE 0 END ) as Hangar,
       SUM( CASE WHEN Dorm_Name = 'Noble' THEN checked_out ELSE 0 END ) as Noble
FROM billeting_history 
GROUP BY Room_Number;

如果您不知道完整的名称列表，那么您需要使用动态 SQL。