• 1阅读
  • 0回复

每年工作量统计-sql

在线 大火腿
级别: 总版主
楼主   发表于: 2021-01-07 , 来自: From:北京市
--利用统计 电子文档利用无权限申请                
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
发帖 回复
« 返回列表
描述
快速回复
如果您提交过一次失败了,可以用”恢复数据”来恢复帖子内容