17,377
社区成员
发帖
与我相关
我的任务
分享
还有我想指定增加
c开头的单位和d开头的单位
不想增加其他字母开头的单位统计
select
nvl(dw,'合计') dw,
count(1) 全部,
sum(case when trunc(sysdate)=trunc(rq) then 1
else 0
end
) 当日,
sum(case when trunc(sysdate)-trunc(rq) <=3 then 1
else 0
end
) 三日,
sum(case when trunc(sysdate)-trunc(rq) <=7 then 1
else 0
end
) 七日,
sum(case when trunc(sysdate)-trunc(rq) <=30 then 1
else 0
end
) 一个月
from table
group by dw
with rollup
union all
select
substr(dw,1,1) || '单位' dw,
count(1) 全部,
sum(case when trunc(sysdate)=trunc(rq) then 1
else 0
end
) 当日,
sum(case when trunc(sysdate)-trunc(rq) <=3 then 1
else 0
end
) 三日,
sum(case when trunc(sysdate)-trunc(rq) <=7 then 1
else 0
end
) 七日,
sum(case when trunc(sysdate)-trunc(rq) <=30 then 1
else 0
end
) 一个月
from table where substr(dw,1,1) in ('c' , 'd')
group by substr(dw,1,1) || '单位'
select
dw,
count(1) 全部,
sum(case when trunc(sysdate)=trunc(rq) then 1
else 0
end
) 当日,
sum(case when trunc(sysdate)-trunc(rq) <=3 then 1
else 0
end
) 三日,
sum(case when trunc(sysdate)-trunc(rq) <=7 then 1
else 0
end
) 七日,
sum(case when trunc(sysdate)-trunc(rq) <=30 then 1
else 0
end
) 一个月
from table
group by dw
with rollup
union all
select
substr(dw,1,1) || '单位' dw,
count(1) 全部,
sum(case when trunc(sysdate)=trunc(rq) then 1
else 0
end
) 当日,
sum(case when trunc(sysdate)-trunc(rq) <=3 then 1
else 0
end
) 三日,
sum(case when trunc(sysdate)-trunc(rq) <=7 then 1
else 0
end
) 七日,
sum(case when trunc(sysdate)-trunc(rq) <=30 then 1
else 0
end
) 一个月
from table
group by substr(dw,1,1) || '单位'
select
dw,
sum(case when trunc(sysdate)=trunc(rq) then 1
else 0
end
) 当日,
sum(case when trunc(sysdate)-trunc(rq) <=3 then 1
else 0
end
) 三日,
sum(case when trunc(sysdate)-trunc(rq) <=7 then 1
else 0
end
) 七日,
sum(case when trunc(sysdate)-trunc(rq) <=30 then 1
else 0
end
) 一个月
from table
group by dw
union all
select
substr(dw,1,1) || '单位' dw,
sum(case when trunc(sysdate)=trunc(rq) then 1
else 0
end
) 当日,
sum(case when trunc(sysdate)-trunc(rq) <=3 then 1
else 0
end
) 三日,
sum(case when trunc(sysdate)-trunc(rq) <=7 then 1
else 0
end
) 七日,
sum(case when trunc(sysdate)-trunc(rq) <=30 then 1
else 0
end
) 一个月
from table
group by substr(dw,1,1) || '单位'