根据字段列统计参考笔者另一篇文章:
Oracle数据库根据某个字段统计不同类型(行)和相同类型(列)的数据
业务需求
sql语句
SELECT
id as 序号,
name as 名称,
count( * ) 总数,
SUM( CASE WHEN TO_CHAR(CREATE_DATE,'YYYY-MM-DD')=TO_CHAR(SYSDATE,'YYYY-MM-DD') THEN 1 ELSE 0 END ) 今日,
SUM( CASE WHEN TO_CHAR(CREATE_DATE,'YYYY-MM-DD')=TO_CHAR(SYSDATE-1,'YYYY-MM-DD') THEN 1 ELSE 0 END ) 昨日,
SUM( CASE WHEN TO_CHAR(CREATE_DATE,'YYYY-MM')=TO_CHAR(SYSDATE,'YYYY-MM') THEN 1 ELSE 0 END ) 本月,
SUM( CASE WHEN TO_CHAR(CREATE_DATE,'YYYY-MM')=TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYY-MM') THEN 1 ELSE 0 END ) 上月
FROM
TABLE_INFO
GROUP BY ID,NAME
ORDER BY ID