数据库根据时间范围 (天,月,季,年) 汇总查询 SQL
程序员文章站
2022-07-14 13:17:34
...
需求说明
今天遇到一个需求,需要按照(天,月,季,年) 的进行查询求和
数据库字段如下图
- dayplan(计划销量)
- yueld:(实际销量)
- currentdate:数据时间,当天可能存在多个数据
现在需要我们根据(天,月,季,年)分别将 销量求和统计出来
分析过程
我的做法是:根据时间范围将数据生成一个字段生成一个单独的字段,然后再分组求和:
上代码
SELECT
dayplan,
yield,
currentdate,
year(currentdate)*10000+1+100 as [YEARKey],
year(currentdate)*10000+1+100*(month(currentdate)-(month(currentdate)-1)%3) as [QuarterKey],
year(currentdate)*10000+1+ month(currentdate)*100 as [MouthKey],
year(currentdate)*10000+ month(currentdate)*100+DAY(currentdate) as [DAYKey]--year(currentdate)*10000+ month(currentdate)*100+DAY(currentdate) as [DAYKey]
FROM
[jt_dm_workfacedayreport]
这样我们看看查询出的结果
我们可以看到
- YEARKey:将每年表示为
yyyy0101
- QuarterKey:将没季度表示为:
yyyy季度第一个月01
- MouthKey:将每月表示为
yyyyMM01
- DAYKey:将没天表示为
yyyyMMdd
这个表示的格式和我业务有关,大家可以根据需要修改为其他的表示方式,原理类似
完成实例
然后我们根据关键对字段进行分组查询 就好了
例如:按月查询,则对MouthKey 进行查询
SELECT A.MouthKey as TIMEKey, sum(A.dayplan) as VALUE1 , SUM(A.yield) as VALUE2
FROM
(
select
dayplan,yield,
currentdate,
year(currentdate)*10000+1+100 as [YEARKey],
year(currentdate)*10000+1+100*(month(currentdate)-(month(currentdate)-1)%3) as [QuarterKey],
year(currentdate)*10000+1+ month(currentdate)*100 as [MouthKey],
year(currentdate)*10000+ month(currentdate)*100+DAY(currentdate) as [DAYKey]
from [jt_dm_workfacedayreport]
) as A
GROUP BY A.MouthKey
结果如下:
其他的只需要将GROUP BY
更改为 YEARKey
,QuarterKey
,DAYKey
则可以了