如何从下面的时间戳列中在单个查询中选择不同年份和不同月份
我试过select distinct year(sc_ts) as years from posts where user_id = 1 order by years asc
,但不知怎么的,我无法将月份部分放入其中。或者这需要两个不同的语句和php处理。
我打算将其输出为
2009 - Mar, Apr, May
2010 - Apr, Jun, Jul
etc
"2009-03-22 16:07:48"
"2009-04-22 16:07:48"
"2009-05-22 16:07:48"
"2010-04-22 16:07:48"
"2010-06-22 16:07:48"
"2010-07-22 16:07:48"
"2011-01-22 16:07:48"
"2011-02-22 16:07:48"
"2011-05-22 16:07:48"
"2011-06-22 16:07:48"
"2011-08-22 16:07:48"
"2012-01-22 16:07:48"
"2012-02-22 16:07:48"
"2012-03-22 16:07:48"
"2012-04-22 16:07:48"
"2012-08-22 16:07:48"
select distinct year(sc_ts) as years, group_concat(monthname(sc_ts)) as months from posts where user_id = 1 order by years asc
编辑:我最初使用month()
,它给出了月份编号,使用monthname()
返回月份名称。
SELECT x.`Year`, GROUP_CONCAT(x.`Month`) AS Months
FROM
(
SELECT DISTINCT YEAR(sc_ts) AS `Year`, MONTHNAME(sc_ts) AS `Month`
FROM
`posts`
WHERE
user_id = 1
ORDER BY
MONTH(sc_ts)
) x
GROUP BY
x.`Year`
ORDER BY
x.`Year`
尝试SELECT distinct SUBSTRING(year(sc_ts), 6)