--利用统计 电子文档利用无权限申请
create table ly2020 as (select sd.qzh,sd.mmm, sd.sll,sd.qxlx||'' qxlx ,qq.qzmc from s_qzh qq join (
select qzh ,to_char(sqsj,'MM') mmm ,qxlx, count(1) sll from (
select t.keyword,u.qzh, t.sqsj,t.qxlx from s_tmpright t join (select u.usercode,g.qzh from s_user u join s_group g on u.pid=g.did) u
on t.sqrdm=u.usercode
where to_char(sqsj,'YYYY') ='2020'
) lll group by qzh, to_char(sqsj,'MM') ,qxlx
) sd on qq.qzh=sd.qzh )
update ly2020 set qxlx='电子浏览' where qxlx='1';
update ly2020 set qxlx='电子下载' where qxlx='2';
update ly2020 set qxlx='实物借阅' where qxlx='3';
update ly2020 set qxlx='电子打印' where qxlx='4';
update ly2020 set qxlx='实物借阅' where qxlx='5';
update ly2020 set qxlx='实物复印' where qxlx='6';
commit;
select qzh,qzmc 全宗名称, qxlx 利用方式 ,mmm 月份, sll 数量 from ly2020 order by qzh,mmm
select qxlx ,count(1) from ly2020 group by qxlx
--年度统计
select qzmc , qxlx ,sum( sll) from ly2020 group by qzmc,qxlx order by qzmc,qxlx
-- 电子文件浏览（包含有权限）
SELECT QZMC,COUNT(1) CS FROM (
select Q.QZMC, N.* from s_netutillog N LEFT JOIN S_QZH Q ON N.QZH=Q.QZH where to_char(N.UTILIZETIME,'YYYY') ='2020'
) T GROUP BY QZMC ORDER BY CS DESC
