需要帮助以某种方式构造查询输出

We have a table which contains TV channel listings.

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

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)

My web page shows a particular tv channel listing as follows:-

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

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

All News timings are consolidate into a single entry, currently I am consolidating the entries at the application layer, is it possible to do this at the query level.

所有的新闻时间都被合并到一个单独的条目中，目前我正在合并应用层的条目，是否有可能在查询级别进行合并。

2 个解决方案

#1

Try something like this:

试试这样:

  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

If you need it for separate channels, this should work:

如果您需要单独的通道，这应该可以:

  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

#2

Perhaps UNION the non-News along with the News rows. Only the News needs its times concatenated.

也许把非新闻和新闻的争吵结合起来。只有新闻需要把它的时代串联起来。

SELECT event_name,
       event_times
FROM  (
  SELECT
    event_name, event_time
  FROM listing
  WHERE event_name != 'News' 
  UNION
  SELECT event_name, GROUP_CONCAT(event_time SEPARATOR ', ') event_time
  FROM listing
  WHERE event_name = 'News' 
  ) AS All 
ORDER BY event_times ASC

#1