mysql按年、季度、月，统计

您所在的位置：网站首页 › oracle怎么按季度统计 › mysql按年、季度、月，统计

mysql按年、季度、月，统计

2024-07-13 10:35| 来源: 网络整理| 查看: 265

以下是按年、按季度和按月统计SQL查询语句：

按年统计：

SELECT ds.checker, YEAR(ds.create_time) AS settleYear, SUM(ds.quantity) AS quantity, SUM(ds.approval_price) AS approvalPrice FROM data_settle ds WHERE ds.delete_flag = 0 AND ds.approval_status != 0 AND ds.create_time BETWEEN '2021-01-01 00:00:00' AND '2023-12-31 23:59:59' AND ds.checker IS NOT NULL GROUP BY ds.checker, YEAR(ds.create_time)

按季度统计：

SELECT ds.checker, YEAR(ds.create_time) AS settleYear, QUARTER(ds.create_time) AS settleQuarter, SUM(ds.quantity) AS quantity, SUM(ds.approval_price) AS approvalPrice FROM data_settle ds WHERE ds.delete_flag = 0 AND ds.approval_status != 0 AND ds.create_time BETWEEN '2021-01-01 00:00:00' AND '2023-12-31 23:59:59' AND ds.checker IS NOT NULL GROUP BY ds.checker, YEAR(ds.create_time), QUARTER(ds.create_time)

按月统计：

SELECT ds.checker, YEAR(ds.create_time) AS settleYear, MONTH(ds.create_time) AS settleMonth, SUM(ds.quantity) AS quantity, SUM(ds.approval_price) AS approvalPrice FROM data_settle ds WHERE ds.delete_flag = 0 AND ds.approval_status != 0 AND ds.create_time BETWEEN '2021-01-01 00:00:00' AND '2023-12-31 23:59:59' AND ds.checker IS NOT NULL GROUP BY ds.checker, YEAR(ds.create_time), MONTH(ds.create_time)

以上三个查询语句分别实现了按年、按季度和按月的统计功能。按年统计时，使用YEAR(ds.create_time)来获取年份，并在GROUP BY子句中进行相应的分组；按季度统计时，在按年的基础上，使用QUARTER(ds.create_time)来获取季度，并在GROUP BY子句中增加对季度的分组；按月统计时，使用MONTH(ds.create_time)来获取月份，并在GROUP BY子句中增加对月份的分组。希望这样的改造符合您的需求，如果还有其他方面需要改进或有疑问的地方，欢迎随时提出。

【本文地址】

mysql按年、季度、月，统计

mysql按年、季度、月，统计

今日新闻

推荐新闻