带有和IF的MYSQL group_concat

Question

我有一个从我的数据库中获取的日期字段，如下所示；PS这里有更多的查询，它使用连接从日期表中获取日期。

SELECT event,event_name,
GROUP_CONCAT(DATE_FORMAT(ed.date,'%b %e') ORDER BY ed.date SEPARATOR ',') as date2 
FROM events ev
LEFT JOIN event_dates ed ON ev.eid=ed.eid
WHERE ev.yr='2013' 
GROUP BY ev.eid

这产生了这个日期列表，它占用了很大的空间。

May 16,May 23,May 30,Jun 6,Jun 20

我想让它看起来像

   May 16,23,30,Jun 6,20

下面是我尝试过的查询。但是它不起作用，因为子查询似乎不知道事件id。我猜我可能需要把它转换成一个连接？？

SELECT DISTINCT ev.event_id, 
GROUP_CONCAT(DISTINCT DATE_FORMAT(race_date,'%a') ORDER BY race_date 
SEPARATOR ',   ')   as date1,

(SELECT GROUP_CONCAT(dm) FROM
 (SELECT CONCAT(
    MONTHNAME(race_date),
    ' ',
    GROUP_CONCAT(DAY(race_date) ORDER BY race_date)
    ) as dm 
FROM event_dates ed1 WHERE ed1.event_id=ev.event_id  
GROUP BY MONTH(race_date))as t) as date2

FROM events ev
JOIN event_dates ed ON ev.event_id = ed.event_id AND ev.race_year = ed.race_year 
GROUP BY event_id

Answer 1

SELECT GROUP_CONCAT(d) FROM (
  SELECT    CONCAT(
              MONTHNAME(date),
              ' ',
              GROUP_CONCAT(DAY(date) ORDER BY date)
            ) AS d
  FROM      my_table
  GROUP BY  MONTH(date)
) t