在 MySQL 中按月和年分组

2021-11-20 00:00:00 sql date group-by mysql

给定一个每行都有时间戳的表，您将如何格式化查询以适应这种特定的 json 对象格式.

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:

{
  "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.

推荐答案

GROUP BY YEAR(t.summaryDateTime), MONTH(t.summaryDateTime);

就是你想要的.

相关文章