最初,这是我想到的:
SELECT s.date, s.day, COALESCE(GROUP_CONCAT(bm.worker SEPARATOR ' '),'') as Morning,COALESCE(GROUP_CONCAT(be.worker SEPARATOR ' '),'') as Evening
FROM schedule s LEFT JOIN
block bm ON s.id = bm.schedule_id AND bm.shift=1 LEFT JOIN
block be ON s.id = be.schedule_id AND be.shift=2
GROUP BY s.date,s.day
Result:
DATE DAY MORNING EVENING
22/09/2014 Monday Ahmad Abdul Faris Faris
23/09/2014 Tuesday Iqbal
导致Fiddle http://www.sqlfiddle.com/#!2/4dfbc6/25.
正如你所看到的 傍晚的田野包含两次 Faris。所以我使用了两个查询并加入了这些结果。像这样:
SELECT T1.date,T1.day,COALESCE(T1.Morning,'') as Morning,COALESCE(T2.Evening,'') as Evening FROM
(SELECT s.date, s.day, GROUP_CONCAT(bm.worker SEPARATOR ' ') as Morning
FROM schedule s LEFT JOIN
block bm ON s.id = bm.schedule_id AND bm.shift=1
GROUP BY s.date,s.day) T1
JOIN
(SELECT s.date, s.day,GROUP_CONCAT(be.worker SEPARATOR ' ') as Evening
FROM schedule s LEFT JOIN
block be ON s.id = be.schedule_id AND be.shift=2
GROUP BY s.date,s.day) T2
ON T1.Date=T2.Date AND T1.Day=T2.Day
Result:
DATE DAY MORNING EVENING
22/09/2014 Monday Ahmad Abdul Faris
23/09/2014 Tuesday Iqbal
查看结果SQL小提琴 http://www.sqlfiddle.com/#!2/4dfbc6/23.
解释:
我们分别选择“早上”和“晚上”,然后将这两个表与日期和星期连接起来。最后从连接查询中获取结果。
GROUP_CONCAT
用于对具有相同日期和日期的字段进行分组。我们可以用SEPARATOR ' '
用于作为分隔符的空间。如果您删除SEPARATOR ' '
,您将得到用逗号(,)分隔的结果。
COALESCE
用于用空字符串替换 null 值(''
).