在 MySQL 中按月和年分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3366895/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Group by month and year in MySQL
提问by Derek Adair
Given a table with a timestamp on each row, how would you format the query to fit into this specific json object format.
给定一个每行都有时间戳的表，您将如何格式化查询以适应此特定的 json 对象格式。
I am trying to organize a json object into years / months.
我正在尝试将 json 对象组织成年/月。
json to base the query off:
json 以查询为基础：
{
"2009":["August","July","September"],
"2010":["January", "February", "October"]
}
Here is the query I have so far -
这是我到目前为止的查询 -
SELECT
MONTHNAME(t.summaryDateTime) as month, YEAR(t.summaryDateTime) as year
FROM
trading_summary t
GROUP BY MONTH(t.summaryDateTime) DESC";
The query is breaking down because it is (predictably) lumping together the different years.
查询正在崩溃，因为它（可预见地）将不同年份混为一谈。
回答by Mitch Dempsey
GROUP BY YEAR(t.summaryDateTime), MONTH(t.summaryDateTime) DESC;
is what you want.
是你想要的。
回答by Ferhat KO?ER
GROUP BY DATE_FORMAT(summaryDateTime,'%Y-%m')
回答by Sasindu H
SELECT MONTHNAME(t.summaryDateTime) as month, YEAR(t.summaryDateTime) as year
FROM trading_summary t
GROUP BY YEAR(t.summaryDateTime) DESC, MONTH(t.summaryDateTime) DESC
Should use DESC for both YEAR and Month to get correct order.
应该对 YEAR 和 Month 使用 DESC 以获得正确的顺序。
回答by John McDonnell
I prefer
我更喜欢
SELECT
MONTHNAME(t.summaryDateTime) as month, YEAR(t.summaryDateTime) as year
FROM
trading_summary t
GROUP BY EXTRACT(YEAR_MONTH FROM t.summaryDateTime) DESC";
回答by Arth
I know this is an old question, but the following should work if you don't need the month name at the DB level:
我知道这是一个老问题，但如果您不需要数据库级别的月份名称，以下应该可以工作：
SELECT EXTRACT(YEAR_MONTH FROM summaryDateTime) summary_year_month
FROM trading_summary
GROUP BY summary_year_month;
You will probably find this to be better performing.. and if you are building a JSON object in the application layer, you can do the formatting/ordering as you run through the results.
您可能会发现这样的性能更好……如果您在应用程序层构建 JSON 对象，您可以在运行结果时进行格式化/排序。
N.B.I wasn't aware you could add DESC to a GROUP BY clause in MySQL, perhaps you are missing an ORDER BY clause:
注意，我不知道您可以将 DESC 添加到 MySQL 中的 GROUP BY 子句，也许您缺少 ORDER BY 子句：
SELECT EXTRACT(YEAR_MONTH FROM summaryDateTime) summary_year_month
FROM trading_summary
GROUP BY summary_year_month
ORDER BY summary_year_month DESC;
回答by michal
You must do something like this
你必须做这样的事情
SELECT onDay, id,
sum(pxLow)/count(*),sum(pxLow),count(`*`),
CONCAT(YEAR(onDay),"-",MONTH(onDay)) as sdate
FROM ... where stockParent_id =16120 group by sdate order by onDay
回答by rebaimelek
use EXTRACT function like this
像这样使用 EXTRACT 函数
mysql> SELECT EXTRACT(YEAR FROM '2009-07-02');
-> 2009
回答by Niclausel
You cal also do this
你也可以这样做
SELECT SUM(amnt) `value`,DATE_FORMAT(dtrg,'%m-%y') AS label FROM rentpay GROUP BY YEAR(dtrg) DESC, MONTH(dtrg) DESC LIMIT 12
to order by year and month. Lets say you want to order from this year and this month all the way back to 12 month
按年和月订购。假设您想从今年和本月一直订购到 12 个月
回答by CTBrewski
This is how I do it:
这就是我的做法：
GROUP BY EXTRACT(YEAR_MONTH FROM t.summaryDateTime);
回答by Eduardo Rascon
You are grouping by month only, you have to add YEAR() to the group by
您仅按月分组，您必须将 YEAR() 添加到组中
