以下是按年、按季度和按月统计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子句中增加对月份的分组。希望这样的改造符合您的需求,如果还有其他方面需要改进或有疑问的地方,欢迎随时提出。