mysql - 带有字段 select as 的 select 语句(这叫什么？)

Question

这是我正在尝试做的一个示例:

我必须按月/年组织一个事件列表以及事件计数，然后是这些事件 ID 的逗号列表。

这是我所拥有的:

     SELECT count(*) as counter, 
                   MONTHNAME(publishDate) as month, 
                   YEAR(publishDate) as year 
              FROM bursch.events 
     GROUP BY YEAR(publishDate), 
              MONTH(publishDate) 
     order by year desc;

但是，此外，我也需要这些 ID (2,5,6) <<< 像这样:

这是一个概念:

SELECT count(*) as counter, 
       MONTHNAME(publishDate) as month, 
       YEAR(publishDate) as year,
       (select id 
          from events 
          where 'call conditions affect the main query') as IDList 
              FROM events 
     GROUP BY YEAR(publishDate), 
              MONTH(publishDate) 
     order by year desc;

这会导致这样的结果:

counter  |  month   | year  | ids
-----------------------------------------
3        |  June    | 2013  | 45,49,50
4        |  July    | 2013  | 39,40,41,42
2        |  March   | 2011  | 33,34
5        |  May     | 2011  | 27,29,30,31,32
1        |  June    | 2011  | 22
4        |  July    | 2011  | 14,17,18,19
1        |  January | 2010  | 13

如果我可以将选择结果基于主选择语句的条件，那就太好了。可能有更好的方法来获得这个结果。也许有更简单的方法。但选择作为字段部分。那会叫什么？相关查询？没有把握。我已经看到我可以在 Oracle 和 MySQL 中做到这一点，并且它很方便(如果我只知道它叫什么)。

提前致谢。如果有不清楚的地方请告诉我。

Answer 1

MySQL有一个名为GROUP_CONCAT()的函数它连接行而不是列。

SELECT COUNT(*) as counter, 
       MONTHNAME(publishDate) as month, 
       YEAR(publishDate) as year,
       GROUP_CONCAT(id) as IDs
FROM   events 
GROUP  BY YEAR(publishDate), 
          MONTH(publishDate) 
ORDER  BY year desc;