oracle数据统计

[qq_28398475]

如图所示，根据工作人员分组，create_date字段统计今天、本周、本月的数据量。要用一条sql怎么实现

[碧水幽幽泉]

本周是指周一到周日吗？

[碧水幽幽泉]

由于你没有给出字段类型和数据格式，我这里假设create_date为date类型，数据格式为:yyyy-mm-dd
假设工作人员字段名称为person

select person,count(*),'今天' type
   from table_name
  where create_date = trunc(sysdate)
  group by person
union all
select person,count(*),'本周' type
   from table_name
  where create_date in (trunc(sysdate)-to_char(sysdate,'d')+2+level-1 from dual connect by level <= 7)
  group by person
union all
select person,count(*),'本月' type
   from table_name
  where create_date in (select trunc(sysdate,'mm')+level-1 from dual connect by level <= last_day(trunc(sysdate,'mm'))-trunc(sysdate,'mm')+1)
  group by person;

[碧水幽幽泉]

用行转列调整下显示:

select person
 from(
select person,count(*) cnt,'今天' flag
   from table_name
  where create_date = trunc(sysdate)
  group by person
union all
select person,count(*) cnt,'本周' flag
   from table_name
  where create_date in (trunc(sysdate)-to_char(sysdate,'d')+2+level-1 from dual connect by level <= 7)
  group by person
union all
select person,count(*) cnt,'本月' flag
   from table_name
  where create_date in (select trunc(sysdate,'mm')+level-1 from dual connect by level <= last_day(trunc(sysdate,'mm'))-trunc(sysdate,'mm')+1)
  group by person
union all
select person,count(*) cnt,'本年' flag
   from table_name
  where trunc(create_date,'year')  = trunc(sysdate,'year') 
  group by person
select person,count(*) cnt,'系统至今' flag
   from table_name
  group by person
  )
pivot(sum(cnt) for flag in('今天','本周','本月','本年','系统至今')