按年，月，日，季度，周分组统计某张表数据的发生量。

--按年度分组统计

SELECT TO_CHAR(CREATION_DATE,\'YYYY-MM\') CREATION_DATE,COUNT(*)
FROM MTL_SYSTEM_ITEMS_B
GROUP BY TO_CHAR(CREATION_DATE,\'YYYY-MM\')
ORDER BY CREATION_DATE DESC;

--按月份分组统计
SELECT TO_CHAR(CREATION_DATE,\'YYYY-MM\') CREATION_DATE,COUNT(*)
FROM MTL_SYSTEM_ITEMS_B
GROUP BY TO_CHAR(CREATION_DATE,\'YYYY-MM\')
ORDER BY CREATION_DATE DESC;

--按日分组统计
SELECT TO_CHAR(CREATION_DATE,\'YYYY-MM-DD\') CREATION_DATE,COUNT(*)
FROM MTL_SYSTEM_ITEMS_B
GROUP BY TO_CHAR(CREATION_DATE,\'YYYY-MM-DD\')
ORDER BY CREATION_DATE DESC;

--按季度分组统计
SELECT TO_CHAR(CREATION_DATE,\'YYYY-Q\') CREATION_DATE,COUNT(*)
FROM MTL_SYSTEM_ITEMS_B
GROUP BY TO_CHAR(CREATION_DATE,\'YYYY-Q\')
ORDER BY CREATION_DATE DESC;

--按周分组统计
SELECT TO_CHAR(CREATION_DATE,\'YYYY-IW\') CREATION_DATE,COUNT(*)
FROM MTL_SYSTEM_ITEMS_B
GROUP BY TO_CHAR(CREATION_DATE,\'YYYY-IW\')
ORDER BY CREATION_DATE DESC;

ITPub博客

统计类SQL（按时间）

最新文章