sql - 在以某种方式制定查询输出方面需要帮助

Question

我们有一个包含电视 channel 列表的表格。

create table listing (id, channel_name, event_name, event_time)

a) listing (1, "ABC Channel", "All My Children", 6.00 AM)
b) listing (2, "ABC Channel", "News", 7 AM)
c) listing (3, "ABC Channel", "One life to live", 7.15 AM)
d) listing (4, "ABC Channel", "General Hospital", 8.00 AM)
e) listing (5, "ABC Channel", "News", 9.00 AM)
f) listing (6, "ABC Channel", "Movie 1", 9.15 AM)
g) listing (7, "ABC Channel", "News", 10.00 AM)

我的网页显示一个特定的电视 channel 列表如下:-

event_name        event_times
---------------------------------------------
All My Children   6.00 AM
News              7.00 AM, 9.00 AM, 10.00 AM
One life to live  7.15 AM
General Hospital  8.00 AM
Movie 1           9.15 AM

所有的News时间都合并到一个条目中，目前我正在合并应用层的条目，是否可以在查询级别执行此操作。

Answer 1

尝试这样的事情:

  SELECT event_name,
         GROUP_CONCAT(event_time SEPARATOR ', ' ORDER BY event_time) AS event_times
    FROM listing
GROUP BY event_name
ORDER BY event_name

如果您需要它用于单独的 channel ，这应该可行:

  SELECT event_name,
         GROUP_CONCAT(event_time SEPARATOR ', ' ORDER BY event_time) AS event_times
    FROM listing
   WHERE channel_name = 'ABC Channel'
GROUP BY event_name
ORDER BY event_name