根据时间表数据创建时间表视图

丹尼舍

目前，我正在PHP和中使用Codeigniter框架开发时间表程序MySQL。我的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 |           |          |         |
+---+---------+---------+-----------+----------+---------+

我将不胜感激有关以上述格式创建表格的任何帮助。

乔治·贝索斯（Giorgos Betsos）

试试这个：

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

演示在这里

编辑：

您似乎想按分组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