oracle中按时间统计次数的语句

来源：互联网发布：linux搭建文件服务器编辑：程序博客网时间：2024/06/04 19:29

按天统计

SELECT TO_CHAR(T.create_time, 'YYYY-MM-DD') TIME, COUNT(*) COUNT  FROM test T GROUP BY TO_CHAR(T.create_time, 'YYYY-MM-DD') ORDER BY TO_CHAR(T.create_time, 'YYYY-MM-DD') ASC NULLS LAST

按周统计

SELECT TO_CHAR(T.create_time, 'YYYY') YEAR,       TO_CHAR(T.create_time, 'IW') TIME,       COUNT(*) COUNT  FROM test T GROUP BY TO_CHAR(T.create_time, 'IW'), TO_CHAR(T.trade_time, 'YYYY') ORDER BY TO_CHAR(T.create_time, 'YYYY'),          TO_CHAR(T.create_time, 'IW') ASC NULLS LAST

按月统计

SELECT TO_CHAR(T.create_time, 'YYYY-MM') TIME, COUNT(*) COUNT  FROM test T GROUP BY TO_CHAR(T.create_time, 'YYYY-MM') ORDER BY TO_CHAR(T.create_time, 'YYYY-MM') ASC NULLS LAST

按季度统计

SELECT TO_CHAR(T.create_time, 'YYYY') YEAR,       TO_CHAR(T.create_time, 'Q') TIME,       COUNT(*) COUNT  FROM test T GROUP BY TO_CHAR(T.create_time, 'Q'), TO_CHAR(T.create_time, 'YYYY') ORDER BY TO_CHAR(T.create_time, 'YYYY'),          TO_CHAR(T.create_time, 'Q') ASC NULLS LAST

按年统计

SELECT TO_CHAR(T.create_time, 'YYYY') YEAR, COUNT(*) COUNT  FROM test T GROUP BY TO_CHAR(T.create_time, 'YYYY') ORDER BY TO_CHAR(T.create_time, 'YYYY') ASC NULLS LAST

注：create_time为表TEST里的创建时间字段，时间类型
以上代码可直接在数据库里运行

阅读全文

0 0