mysql - 如何为多人的日程制作一个mysql View ？

Question

我有一个数据表来描述一个人的预定时间。看起来像这样

Name | day | start_hour | end_hour
-
Tom | Monday   | 8  | 12
Tom | Weekend  | 16 | 20
Dick| Weekdays | 9  | 15
Sue | Sunday   | 7  | 23

我怎样才能做出这样的 View ？

Hr | M         | Tu   | W    | Th  | F    | Sa | Su
...
8  | Tom       |      |      |     |      |    | 
9  | Tom, Dick | Dick | Dick | Dick| Dick |    | 
...

每天每小时的 View 以及该时间安排的人员。

Answer 1

如果您有 hours 表(1 个整数列 hour，值 0-23):

SELECT
   hour,
   GROUP_CONCAT(DISTINCT IF(day='Monday'    OR day='Weekdays',name,null) SEPARATOR ','),
   GROUP_CONCAT(DISTINCT IF(day='Tuesday'   OR day='Weekdays',name,null) SEPARATOR ','),
   GROUP_CONCAT(DISTINCT IF(day='Wednesday' OR day='Weekdays',name,null) SEPARATOR ','),
   GROUP_CONCAT(DISTINCT IF(day='Thursday'  OR day='Weekdays',name,null) SEPARATOR ','),
   GROUP_CONCAT(DISTINCT IF(day='Friday'    OR day='Weekdays',name,null) SEPARATOR ','),
   GROUP_CONCAT(DISTINCT IF(day='Saturday'  OR day='Weekend', name,null) SEPARATOR ','),
   GROUP_CONCAT(DISTINCT IF(day='Sunday'    OR day='Weekend', name,null) SEPARATOR ',')
FROM hours 
LEFT JOIN schedule
ON start_hour <= hour AND end_hour > hour OR end_hour = 0
GROUP BY hour;