oracle中按年，季，月，天，地区统计某表发送数据的总数

• 按年度统计每年短信发送成功的条数

  select to_char(t.TIME,‘yyyy’) AS 年度,sum(1) as 数量 from
  tableuser t WHERE RESULT=‘2’ group by
  to_char(t.TIME,‘yyyy’) ORDER BY 年度

• 按月统计某年发送成功总数

  select to_char(t.TIME,‘YYYY-MM’) as 月份,sum(1) as 数量
  from tableuser t
  WHERE 1 = 1 and t.RESULT=‘2’ and YEAR=‘2018’
  GROUP BY to_char(t.TIME,‘YYYY-MM’) ORDER BY 月份

• 按季度统计某年发送成功总数

  select to_char(t.TIME,‘q’) 季度,sum(1) as 数量 from
  tableuser t WHERE 1 = 1 and t.RESULT=‘2’ and
  t.YEAR=‘2018’ group by to_char(t.TIME,‘q’) ORDER BY 季度 NULLS
  LAST;

• 按天统计某月中每天发送的总数，包括当天

  select to_char(t.TIME+1, ‘YYYY-MM-DD’) as 天,sum(1) as 数量 from
  tableuser t WHERE t.YEAR=‘2018’ and t.RESULT=‘2’
  and t.MONTH =‘1’ group by to_char(t.TIME+1, ‘YYYY-MM-DD’)
  ORDER by 天 NULLS LAST;

• 查询该月各地市统计数据

  select ts.destaddr,to_char(t.createtime,‘yyyy-MM-dd’),count() from
  tableuser0 t,tableuser1 ts where
  t.mobilephone=ts.mobilephone and
  to_char(t.createtime,‘yyyy-MM’)=‘2015-06’ group by
  ts.destaddr,to_char(t.createtime,‘yyyy-MM-dd’) order by
  to_char(t.createtime,‘yyyy-MM-dd’),count() asc

• 查询该月各省份统计数据

  select ts.province,to_char(t.createtime,‘yyyy-MM-dd’),count() from
  tableuser0 t,tableuser1 ts where
  t.mobilephone=ts.mobilephone
  and to_char(t.createtime,‘yyyy-MM’)=‘2015-06’
  group by ts.province,to_char(t.createtime,‘yyyy-MM-dd’)
  order by to_char(t.createtime,‘yyyy-MM-dd’),count() asc