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')

select to_char(t.start_time, 'YYYY-MM-DD' ) day , count (*) from test t

where to_char(t.start_time, 'YYYY' )= '2019' --条件限制

group by to_char(t.start_time, 'YYYY-MM-DD' ) --分组

order by to_char(t.start_time, 'YYYY-MM-DD' ) --排序

select to_char(t.start_time, 'YYYY' ) year ,to_char(t.start_time, 'IW' ), count (*) from test t

where to_char(t.start_time, 'YYYY' )= '2019' --条件限制

group by to_char(t.start_time, 'YYYY' ) year ,to_char(t.start_time, 'IW' ) --分组

order by to_char(t.start_time, 'YYYY' ) year ,to_char(t.start_time, 'IW' ) --排序

select to_char(t.start_time, 'YYYY-MM' ) , count (*) from test t

where to_char(t.start_time, 'YYYY' )= '2019' --条件限制

group by to_char(t.start_time, 'YYYY-MM' ) --分组

order byto_char(t.start_time, 'YYYY-MM' ) --排序

select to_char(t.start_time, 'YYYY' ) year ,to_char(t.start_time, 'Q' ), count (*) from test t

where to_char(t.start_time, 'YYYY' )= '2019' --条件限制

group by to_char(t.start_time, 'YYYY' ) ,to_char(t.start_time, 'Q' ) --分组

order byto_char(t.start_time, 'YYYY' ) ,to_char(t.start_time, 'Q' ) --排序

select to_char(t.start_time, 'YYYY' ) year , count (*) from test t

where to_char(t.start_time, 'YYYY' )= '2019' --条件限制

group by to_char(t.start_time, 'YYYY' ) --分组

order by to_char(t.start_time, 'YYYY' ) --排序

create table CONSUMER_ACC

  (

  ID VARCHAR2(50) not null ,

  ACC_NUM VARCHAR2(10),

  DATETIME DATE

  )

  select t.id,trunc(t.datetime, 'mm' ) as d, sum (t.acc_num) as n

  from CONSUMER_ACC t

  --where

  group by t.id,trunc(t.datetime, 'mm' )

  order by n desc ;

  select t.id,to_char(t.datetime, 'mm' ) d , sum (t.acc_num) n

  from CONSUMER_ACC t

  --where

  group by t.id,to_char(t.datetime, 'mm' )

  order by n desc

------------------------------------------------------------------------------

//按天统计 

select count (dataid) as 每天操作数量, sum ()

from

where

group by trunc(createtime, 'DD' ))

//按自然周统计 

select to_char( date , 'iw' ), sum () 

from

where

group by to_char( date , 'iw' ) 

//按自然月统计 

select to_char( date , 'mm' ), sum () 

from

where

group by to_char( date , 'mm' ) 

//按季统计 

select to_char( date , 'q' ), sum () 

from

where

group by to_char( date , 'q' ) 

//按年统计 

select to_char( date , 'yyyy' ), sum () 

from

where

group by to_char( date , 'yyyy' )