在MySQL中按月份和年份分组

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

是你想要的。

GROUP BY DATE_FORMAT(summaryDateTime,'%Y-%m')

我更喜欢

SELECT
    MONTHNAME(t.summaryDateTime) as month, YEAR(t.summaryDateTime) as year
FROM
    trading_summary t 
GROUP BY EXTRACT(YEAR_MONTH FROM t.summaryDateTime);

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

应使用DESC的年和月,以获得正确的顺序。

  SELECT EXTRACT(YEAR_MONTH FROM summaryDateTime) summary_year_month 
    FROM trading_summary  
GROUP BY summary_year_month;

See EXTRACT function docs

注意。 我不知道您可以在MySQL中将DESC添加到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;

你必须这样做

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

SELECT  SUM(amnt) `value`,DATE_FORMAT(dtrg,'%m-%y') AS label FROM rentpay GROUP BY YEAR(dtrg) DESC, MONTH(dtrg) DESC LIMIT 12

我就是这样做的:

GROUP BY  EXTRACT(YEAR_MONTH FROM t.summaryDateTime);

像这样使用提取函数

mysql> SELECT EXTRACT(YEAR FROM '2009-07-02');
       -> 2009

SELECT YEAR(t.summaryDateTime) as yr, GROUP_CONCAT(MONTHNAME(t.summaryDateTime)) AS month 
FROM trading_summary t GROUP BY yr

仍然需要在外部脚本中对其进行处理,以获得所需的结构。

例如,使用PHP的explode从月份名称列表创建一个数组,然后使用json\u encode()

GROUP BY year, month DESC";

而不是

GROUP BY MONTH(t.summaryDateTime) DESC";