oracle按时间分组查询语句
--某一时间段按年分组查询数据
SELECT TO_CHAR(A.APPLICATION_DATE,'YYYY') GROUPSTYLE,COUNT(*) DATECOUNT
FROM APPLY_TO_ROAD A
WHERE A.APPLICATION_DATE BETWEEN TO_DATE('2008-06-01','YYYY-MM-DD') AND SYSDATE
GROUP BY TO_CHAR(A.APPLICATION_DATE,'YYYY');
--某一时间段按月分组查询数据
SELECT TO_CHAR(A.APPLICATION_DATE,'YYYY-MM') GROUPSTYLE,COUNT(*) DATECOUNT
FROM APPLY_TO_ROAD A
WHERE A.APPLICATION_DATE BETWEEN TO_DATE('2008-6','YYYY-MM') AND SYSDATE
GROUP BY TO_CHAR(A.APPLICATION_DATE,'YYYY-MM');
--某一时间段按天分组查询数据
SELECT TO_CHAR(A.APPLICATION_DATE,'YYYY-MM-DD') GROUPSTYLE,COUNT(*) DATECOUNT
FROM APPLY_TO_ROAD A
WHERE A.APPLICATION_DATE BETWEEN TO_DATE('2010-6-1','YYYY-MM-DD') AND SYSDATE
GROUP BY TO_CHAR(A.APPLICATION_DATE,'YYYY-MM-DD');
--某一时间段按季分组查询数据
SELECT TO_CHAR(A.APPLICATION_DATE,'Q') GROUPSTYLE,COUNT(*) DATECOUNT
FROM APPLY_TO_ROAD A
WHERE A.APPLICATION_DATE BETWEEN TO_DATE('2010-6-1','YYYY-MM-DD') AND SYSDATE
GROUP BY TO_CHAR(A.APPLICATION_DATE,'Q');
--某一时间段按周分组查询数据
SELECT TO_CHAR(A.APPLICATION_DATE,'IW') GROUPSTYLE,COUNT(*) DATECOUNT
FROM APPLY_TO_ROAD A
WHERE A.APPLICATION_DATE BETWEEN TO_DATE('2010-6-1','YYYY-MM-DD') AND SYSDATE
GROUP BY TO_CHAR(A.APPLICATION_DATE,'IW');
参考资料连接:http://westlife063.blog.163.com/blog/static/12994209620103309313615/
SELECT TO_CHAR(A.APPLICATION_DATE,'YYYY') GROUPSTYLE,COUNT(*) DATECOUNT
FROM APPLY_TO_ROAD A
WHERE A.APPLICATION_DATE BETWEEN TO_DATE('2008-06-01','YYYY-MM-DD') AND SYSDATE
GROUP BY TO_CHAR(A.APPLICATION_DATE,'YYYY');
--某一时间段按月分组查询数据
SELECT TO_CHAR(A.APPLICATION_DATE,'YYYY-MM') GROUPSTYLE,COUNT(*) DATECOUNT
FROM APPLY_TO_ROAD A
WHERE A.APPLICATION_DATE BETWEEN TO_DATE('2008-6','YYYY-MM') AND SYSDATE
GROUP BY TO_CHAR(A.APPLICATION_DATE,'YYYY-MM');
--某一时间段按天分组查询数据
SELECT TO_CHAR(A.APPLICATION_DATE,'YYYY-MM-DD') GROUPSTYLE,COUNT(*) DATECOUNT
FROM APPLY_TO_ROAD A
WHERE A.APPLICATION_DATE BETWEEN TO_DATE('2010-6-1','YYYY-MM-DD') AND SYSDATE
GROUP BY TO_CHAR(A.APPLICATION_DATE,'YYYY-MM-DD');
--某一时间段按季分组查询数据
SELECT TO_CHAR(A.APPLICATION_DATE,'Q') GROUPSTYLE,COUNT(*) DATECOUNT
FROM APPLY_TO_ROAD A
WHERE A.APPLICATION_DATE BETWEEN TO_DATE('2010-6-1','YYYY-MM-DD') AND SYSDATE
GROUP BY TO_CHAR(A.APPLICATION_DATE,'Q');
--某一时间段按周分组查询数据
SELECT TO_CHAR(A.APPLICATION_DATE,'IW') GROUPSTYLE,COUNT(*) DATECOUNT
FROM APPLY_TO_ROAD A
WHERE A.APPLICATION_DATE BETWEEN TO_DATE('2010-6-1','YYYY-MM-DD') AND SYSDATE
GROUP BY TO_CHAR(A.APPLICATION_DATE,'IW');
参考资料连接:http://westlife063.blog.163.com/blog/static/12994209620103309313615/