下面是我尝试做的一个示例:
我必须按月份/年份组织一个事件列表,其中包含事件的计数,然后是这些事件的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)<lt<像这样:
这是一个概念:
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中做到这一点,它派上了用场(如果我知道它叫什么的话)。
提前感谢。如果有什么不清楚的地方,请告诉我。
MySQL有一个名为GROUP_CONNCT()的函数,它连接行而不是列。
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;