从计划数据创建时间表视图

Question

目前，我正在PHP和MySQL中使用Codeigniter框架开发一个时间表程序。我有具有以下数据的schedule表：

+-------------+--------------+-----------+--------+
| Schedule ID | Subject Name | Day       | Period |
+-------------+--------------+-----------+--------+
| 1           | Biology      | Monday    | 1      |
| 2           | Biology      | Wednesday | 2      |
| 3           | Biology      | Friday    | 3      |
| 4           | Physics      | Tuesday   | 8      |
| 5           | Physics      | Thursday  | 6      |
| 6           | Math         | Monday    | 7      |
+-------------+--------------+-----------+--------+

我可以将schedule表的每一行作为数组。我想在以下视图中创建一个时间表(使用HTML表)：

+---+---------+---------+-----------+----------+---------+
| # | Monday  | Tuesday | Wednesday | Thursday | Friday  |
+---+---------+---------+-----------+----------+---------+
| 1 | Biology |         |           |          |         |
+---+---------+---------+-----------+----------+---------+
| 2 |         |         | Biology   |          |         |
+---+---------+---------+-----------+----------+---------+
| 3 |         |         |           |          | Biology |
+---+---------+---------+-----------+----------+---------+
| 4 |         |         |           |          |         |
+---+---------+---------+-----------+----------+---------+
| 5 |         |         |           |          |         |
+---+---------+---------+-----------+----------+---------+
| 6 |         |         |           | Physics  |         |
+---+---------+---------+-----------+----------+---------+
| 7 | Math    |         |           |          |         |
+---+---------+---------+-----------+----------+---------+
| 8 |         | Physics |           |          |         |
+---+---------+---------+-----------+----------+---------+

如能以上述格式创建表格，我将不胜感激。

Answer 1

试试这个：

SELECT ScheduleID,
       MAX(CASE WHEN Day = 'Monday' THEN SubjectName END) AS Monday,
       MAX(CASE WHEN Day = 'Tuesday' THEN SubjectName END) AS Tuesday,
       MAX(CASE WHEN Day = 'Wednesday' THEN SubjectName END) AS Wednesday,
       MAX(CASE WHEN Day = 'Thursday' THEN SubjectName END) AS Thursday,
       MAX(CASE WHEN Day = 'Friday' THEN SubjectName END) AS Friday
FROM mytable
GROUP BY ScheduleID

Demo here

编辑：

您似乎希望按照Period而不是ScheduleID进行分组。您可以创建包含所有可能的Period值的内联表，或者使用查找表(如果已经有)：

SELECT t1.Period,
       MAX(CASE WHEN Day = 'Monday' THEN SubjectName END) AS Monday,
       MAX(CASE WHEN Day = 'Tuesday' THEN SubjectName END) AS Tuesday,
       MAX(CASE WHEN Day = 'Wednesday' THEN SubjectName END) AS Wednesday,
       MAX(CASE WHEN Day = 'Thursday' THEN SubjectName END) AS Thursday,
       MAX(CASE WHEN Day = 'Friday' THEN SubjectName END) AS Friday 
FROM (SELECT 1 AS Period UNION ALL 
      SELECT 2 UNION ALL 
      SELECT 3 UNION ALL 
      SELECT 4 UNION ALL 
      SELECT 5 UNION ALL 
      SELECT 6 UNION ALL 
      SELECT 7 UNION ALL 
      SELECT 8) AS t1
LEFT JOIN mytable AS t2 ON t1.Period = t2.Period
GROUP BY t1.Period

Demo here