在日期字段(字符串）中按月份分组 - Group by month in the date field (string)

Group by month in the date field (string)

本文关键字：日期字段字符串 | 更新日期: 2023-09-27

一个带有字符串字段的表，其中存储日期（不要建议我更改字段类型。这会造成危险）。

+--+----------+------+
|id|date      |amount|
+--+----------+------+
|1 |23-03-2014|5000  |
+--+----------+------+
|2 |25-03-2014|3000  |
+--+----------+------+
|3 |21-04-2014|3000  |
+--+----------+------+
|4 |25-04-2014|4000  |
+--+----------+------+

我想写一个模型函数，它将从表中返回按月份分组的金额总和。我该怎么做？它将显示类似的输出

03 - 8000
04 - 7000

SELECT MONTH(date_field) as mon, SUM(amount) as total FROM table_name GROUP BY MONTH(date_field);

更新：

SELECT SUBSTR(date_field,6,2) as mon, SUM(amount) as total FROM table_name GROUP BY mon;

您可以尝试以下查询

SELECT SUM(amount) FROM table GROUP BY MONTH(date)

尝试MONTH（）

SELECT MONTH(date),SUM(amount) FROM table GROUP BY MONTH(date)