oracle实现按天,周,月,季度,年查询排序方法
oracle按天,周,月,季度,年查询排序
天--to_char(t.start_time,'YYYY-MM-DD') 周--to_char(t.start_time,'YYYY'),to_char(t.start_time,'IW') 月度--to_char(t.start_time,'YYYY-MM') 季度--to_char(t.start_time,'YYYY'),to_char(t.start_time,'Q') 年度--to_char(t.start_time,'YYYY')
按天查询
selectto_char(t.start_time,'YYYY-MM-DD')day,count(*)fromtestt whereto_char(t.start_time,'YYYY')='2019'--条件限制 groupbyto_char(t.start_time,'YYYY-MM-DD')--分组 orderbyto_char(t.start_time,'YYYY-MM-DD')--排序
按周查询
selectto_char(t.start_time,'YYYY')year,to_char(t.start_time,'IW'),count(*)fromtestt whereto_char(t.start_time,'YYYY')='2019'--条件限制 groupbyto_char(t.start_time,'YYYY')year,to_char(t.start_time,'IW')--分组 orderbyto_char(t.start_time,'YYYY')year,to_char(t.start_time,'IW')--排序
按月度查询
selectto_char(t.start_time,'YYYY-MM'),count(*)fromtestt whereto_char(t.start_time,'YYYY')='2019'--条件限制 groupbyto_char(t.start_time,'YYYY-MM')--分组 orderbyto_char(t.start_time,'YYYY-MM')--排序
按季度查询
selectto_char(t.start_time,'YYYY')year,to_char(t.start_time,'Q'),count(*)fromtestt whereto_char(t.start_time,'YYYY')='2019'--条件限制 groupbyto_char(t.start_time,'YYYY'),to_char(t.start_time,'Q')--分组 orderbyto_char(t.start_time,'YYYY'),to_char(t.start_time,'Q')--排序
按年度查询
selectto_char(t.start_time,'YYYY')year,count(*)fromtestt whereto_char(t.start_time,'YYYY')='2019'--条件限制 groupbyto_char(t.start_time,'YYYY')--分组 orderbyto_char(t.start_time,'YYYY')--排序
知识点扩展:oracle实现按天,周,月,季度,年查询统计数据
这里提供了一种方法,挺不错oracle实现按周,月,季度,年查询统计数据。
还在网上看到用trunc来搞也可以,下面是个例子,两句SQL效果一样的.
id有重复的,所以groupby搞了两个字段.
只在Oracle数据库里试过,其它库没试过。
createtableCONSUMER_ACC ( IDVARCHAR2(50)notnull, ACC_NUMVARCHAR2(10), DATETIMEDATE ) selectt.id,trunc(t.datetime,'mm')asd,sum(t.acc_num)asn fromCONSUMER_ACCt --where groupbyt.id,trunc(t.datetime,'mm') orderbyndesc; selectt.id,to_char(t.datetime,'mm')d,sum(t.acc_num)n fromCONSUMER_ACCt --where groupbyt.id,to_char(t.datetime,'mm') orderbyndesc ------------------------------------------------------------------------------ //按天统计 selectcount(dataid)as每天操作数量,sum() from where groupbytrunc(createtime,'DD')) //按自然周统计 selectto_char(date,'iw'),sum() from where groupbyto_char(date,'iw') //按自然月统计 selectto_char(date,'mm'),sum() from where groupbyto_char(date,'mm') //按季统计 selectto_char(date,'q'),sum() from where groupbyto_char(date,'q') //按年统计 selectto_char(date,'yyyy'),sum() from where groupbyto_char(date,'yyyy')
总结
以上所述是小编给大家介绍的oracle实现按天,周,月,季度,年查询排序方法,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持!
如果你觉得本文对你有帮助,欢迎转载,烦请注明出处,谢谢!