php - SQL 查询分组不正确

Question

我需要创建一个将使用以下数据的 SQL 查询:

shift_id, emp_id,   date,    starttime, endtime
   1    ,  55   , 2015-10-14, 06:00:00, 10:00:00
   2    ,  55   , 2015-10-15, 03:00:00, 13:00:00
   3    ,  52   , 2015-10-15, 07:00:00, 14:00:00

然后重新排列它，使其将“日期”显示在顶部作为一列，起始日为下周日。我已经在下面开始尝试，但是如果有 2 名员工(emp_id)在同一天工作，则不会显示分组:

SELECT concat(firstname,' ', surname) AS 'Fname', 
    (CASE WHEN DAYOFWEEK(date) = 1 THEN concat(starttime,'-', endtime) END) `Sunday`,
    (CASE WHEN DAYOFWEEK(date) = 2 THEN concat(starttime,'-', endtime) END) `Monday`,
    (CASE WHEN DAYOFWEEK(date) = 3 THEN concat(starttime,'-', endtime)  END) `Tuesday`,
    (CASE WHEN DAYOFWEEK(date) = 4 THEN concat(starttime,'-', endtime)  END) `Wednesday`,
    (CASE WHEN DAYOFWEEK(date) = 5 THEN concat(starttime,'-', endtime)  END) `Thursday`,
    (CASE WHEN DAYOFWEEK(date) = 6 THEN concat(starttime,'-', endtime)  END) `Friday`,
    (CASE WHEN DAYOFWEEK(date) = 7 THEN concat(starttime,'-', endtime)  END) `Saturday`
FROM shifts NATURAL JOIN employees
GROUP BY Fname, date

这是显示员工姓名两次而不是分组的结果？我哪里出错了？:

使用GROUP_CONCAT，错误结果如下:

Answer 1

您选择的列数多于您在 GROUP BY 中指定的列数。结果，MySQL 将简单地从该组中的一条记录中选取数据来表示它: https://dev.mysql.com/doc/refman/5.5/en/group-by-handling.html

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

尝试这样的事情:

SELECT concat(firstname,' ', surname) AS 'Fname', 
    MAX(CASE WHEN DAYOFWEEK(date) = 1 THEN concat(starttime,'-', endtime) END) `Sunday`,
    MAX(CASE WHEN DAYOFWEEK(date) = 2 THEN concat(starttime,'-', endtime) END) `Monday`,
    MAX(CASE WHEN DAYOFWEEK(date) = 3 THEN concat(starttime,'-', endtime)  END) `Tuesday`,
    MAX(CASE WHEN DAYOFWEEK(date) = 4 THEN concat(starttime,'-', endtime)  END) `Wednesday`,
    MAX(CASE WHEN DAYOFWEEK(date) = 5 THEN concat(starttime,'-', endtime)  END) `Thursday`,
    MAX(CASE WHEN DAYOFWEEK(date) = 6 THEN concat(starttime,'-', endtime)  END) `Friday`,
    MAX(CASE WHEN DAYOFWEEK(date) = 7 THEN concat(starttime,'-', endtime)  END) `Saturday`
FROM shifts NATURAL JOIN employees
GROUP BY Fname, date