问题描述
我目前正在基于Web创建计划系统的项目。我想从数据库中获取数据，并以表（计划格式）显示它。
I am currently at project of creating scheduling system based on web. I want to get data from database, and display it in table (schedule format).
在数据库中，我有2张桌子
In database, I have 2 table
schedule id date day 1 22/09/2014 Monday 2 23/09/2014 Tuesday
block block_id schedule_id worker shift 1 1 Ahmad 1 2 1 Abdul 1 3 1 Faris 2 4 2 Iqbal 2
计划的每一天都有几个块，schedule_id（以块为单位）指的是它应该在哪一天。 Coloumn shift（在块中）将块定义为早晨（shift = 1）和晚上（shift = 2），
Each day of schedule has several block and schedule_id (in block) is refer to which day it should be. Coloumn shift (in block) is defining block as morning (shift=1) and evening (shift=2),
我想显示类似的内容
date day Morning Evening
22/09/2014 Monday Ahmad Faris
Abdul
23/09/2014 Tuesday Iqbal
我尝试创建sql语句，例如
I have tried to create sql statement like
SELECT schedule.date, schedule.day, block.worker FROM schedule LEFT JOIN block ON schedule.id = block.schedule_id
但表的格式不正确
date day Morning Evening 22/09/2014 Monday Ahmad 22/09/2014 Monday Abdul 22/09/2014 Monday Faris 23/09/2014 Tuesday Iqbal
我知道我的陈述中缺少一些内容，以及我如何做到这一点。
I know something missing in my statement, and how i to accomplish this.
感谢您的帮助。
推荐答案
最初，这是我想出的：
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
结果：
DATE DAY MORNING EVENING 22/09/2014 Monday Ahmad Abdul Faris Faris 23/09/2014 Tuesday Iqbal
结果在中。
您可以看到晚上字段包含Faris两次。因此，我使用了两个查询并加入了这些结果。像这样：
As you can see Evening field contains Faris twice. So I used two queries and joined those results. Like this:
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
结果：
DATE DAY MORNING EVENING 22/09/2014 Monday Ahmad Abdul Faris 23/09/2014 Tuesday Iqbal
查看结果。
说明：
我们分别选择早晨和晚上，然后将这两个表与日期和日期结合在一起。最后，从联接的查询中获取结果。
We are selecting for Morning and Evening separately, then we are joining these two tables with date and day. And finally result is fetched from the joined query.
GROUP_CONCAT 用于对日期和日期相同的字段进行分组。我们可以使用 SEPARATOR’作为分隔符。如果删除 SEPARATOR''，将得到以逗号分隔的结果。
GROUP_CONCAT is used to group fields having same date and day. We can use SEPARATOR ' ' for space as separator. If you remove SEPARATOR ' ', you will get the result seprated by comma(,).
COALESCE 用于将空值替换为空字符串（''）。
COALESCE is used to replace null values with empty string('').
这篇关于显示在特定列组中的SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！