如何为一周中的每一天创建对应的开始时间列？

你的问题出在子查询未关联当前员工，导致返回所有周一的记录，触发“子查询返回多个结果”的错误。推荐用条件聚合实现行转列，这是处理这类需求的标准方法：

SELECT 
  `Employee Number`,
  `Employee Name`,
  MAX(CASE WHEN `Day` = 'Monday' THEN `Start Time` END) AS `Monday Start Time`,
  MAX(CASE WHEN `Day` = 'Tuesday' THEN `Start Time` END) AS `Tuesday Start Time`,
  MAX(CASE WHEN `Day` = 'Wednesday' THEN `Start Time` END) AS `Wednesday Start Time`,
  MAX(CASE WHEN `Day` = 'Thursday' THEN `Start Time` END) AS `Thursday Start Time`,
  MAX(CASE WHEN `Day` = 'Friday' THEN `Start Time` END) AS `Friday Start Time`,
  MAX(CASE WHEN `Day` = 'Saturday' THEN `Start Time` END) AS `Saturday Start Time`,
  MAX(CASE WHEN `Day` = 'Sunday' THEN `Start Time` END) AS `Sunday Start Time`
FROM start_times
GROUP BY `Employee Number`, `Employee Name`;

代码说明： #

• GROUP BY Employee Number, Employee Name``：将同一员工的所有日期记录合并为一行
• CASE WHEN Day= 'XXX' THENStart Time END：筛选对应星期的开始时间，非目标日期返回NULL
• MAX()：因每个员工每天仅一条记录，MAX会取出唯一有效时间值（无记录时返回NULL）

如果坚持用子查询写法，需给子查询添加员工关联条件，不过效率不如上述方法：

SELECT 
  DISTINCT `Employee Number`,
  `Employee Name`,
  (SELECT `Start Time` FROM start_times st2 WHERE st2.`Day` = 'Monday' AND st2.`Employee Number` = st1.`Employee Number`) AS `Monday Start Time`,
  (SELECT `Start Time` FROM start_times st2 WHERE st2.`Day` = 'Tuesday' AND st2.`Employee Number` = st1.`Employee Number`) AS `Tuesday Start Time`
  -- 其余星期的子查询以此类推
FROM start_times st1;

内容的提问来源于stack exchange，提问作者ajfurjanic