如何制作一个mysql视图来查看多人的日程？

Question

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

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

我怎样才能制作一个看起来像这样的视图？

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

每天每小时的视图以及该时间段内安排的人员。

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;

Answer 2

我会尝试类似这样的查询。
start_hour start_hour 开始工作

它会告诉你有多少人可以在周一、周二的
8 2 3

SELECT start_hour, SUM(CASE DAY='Moday' THEN 1 END),
        SUM(CASE DAY='Tuesday' THEN 1 END),
        SUM(CASE DAY='Wednesday' THEN 1 END),
 FROM schedule,
 (
 SELECT 'Monday' dayname FROM dual
 UNION
 SELECT 'Tuesday' dayname FROM dual
 UNION
 SELECT 'Wednesday' dayname FROM dual) days,
 WHERE  days.dayname=schedule.dayname
 GROUP BY start_hour

Answer 3

SELECT start_hour, 
        GROUP_CONCAT( (CASE DAY='Moday' THEN null ELSE Name  END),
        GROUP_CONCAT( (CASE DAY='Tuesday' THEN null ELSE Name  END),
        GROUP_CONCAT( (CASE DAY='Wednesday' THEN null ELSE Name  END),
 FROM schedule,
 (
 SELECT 'Monday' dayname FROM dual
 UNION
 SELECT 'Tuesday' dayname FROM dual
 UNION
 SELECT 'Wednesday' dayname FROM dual) days,
 WHERE  days.dayname=schedule.dayname
 GROUP BY start_hour