泛微常用sql语句

• 登陆最少统计：
  select top 5 relatedname,COUNT(*) as c from SysMaintenanceLog where operateitem=60 group by relatedname order by c asc
• 待办事宜数量前十机构统计：
  select top 10 (select SUBSTRING(subcompanyname,3,6) from hrmsubcompany where id=subcompanyid1),count(requestid) as cid from workflow_currentoperator  ,hrmresource where  hrmresource.id =workflow_currentoperator.userid and isremark in (0,1,8,9) and islasttimes=1 group by subcompanyid1 order by cid desc
• 员工数前十机构分布：
  select top 10 (select subcompanyname from hrmsubcompany where id=subCompanyId1),count(id) As cids  from hrmresource group by subCompanyId1 order by cids desc
• 项目统计前十机构：
  select top 10 (select SUBSTRING(subcompanyname,3,6) from hrmsubcompany where id=hrmdepartment.subcompanyid1),count(*)  as cid from Prj_ProjectInfo ,hrmdepartment where Prj_ProjectInfo.department=hrmdepartment.id group by hrmdepartment.subcompanyid1 order by cid desc
• ----oracle:按照月份统计-过生日人员排名前10：
  select * from (select substr(birthday, 6, 2) , count(1) s  from hrmresource where status in (0, 1, 2, 3)   and birthday is not null group by substr(birthday, 6, 2) order by s desc  ) a where rownum<=10;
• --sqlserver:按照月份统计-过生日人员排名前10：
  select top 10 SUBSTRING(birthday,6,2) , count(1) s  from hrmresource  where status in (0, 1, 2, 3)   and birthday is not null and  birthday !='' group by SUBSTRING(birthday,6,2) order by s desc;
• --oracle:登录排名前10：
  select *  from (select (select lastname from HrmResource where id = relatedid) as lastname, count(1) s from SysMaintenanceLog where operatetype = 6 and operateitem = 60 group by relatedid  order by s desc) a where rownum <= 10;
• --sqlserver:登录排名前10：
  select top 10 (select lastname from HrmResource where id =relatedid) as lastname , count(1) s  from SysMaintenanceLog where operatetype = 6   and operateitem = 60 group by relatedid order by s desc;
• 登陆次数排名：
  select * from (select relatedname,count(*) as 系统登陆次数 from SysMaintenanceLog where operatetype = 6 and operatedate between '2012-07-01' and '2020-12-31' group by relatedid,relatedname order by count(*) desc)where rownum<=10
• 流程操作次数：
  select * from (select (select lastname from hrmresource where id = userid),count(*) as 流程操作人次数 from workflow_currentoperator where workflowid!=301 group by userid order by count(*) desc)where rownum<=10
• 发起流程数排名：
  select * from (select  (select lastname from hrmresource where id = creater) as 用户,count(*) as 发起流程次数 from workflow_requestbase where workflowid<>301 group by creater order by count(*) desc)where rownum<=10
• 近期访问统计：
  select * from (select  operatedate,count(id) as acs from SysMaintenanceLog where operateitem=60 and relatedname<>'系统管理员' group by operatedate order by operatedate desc) where rownum<=10
• 待办事宜排名：
  select * from (SELECT (select lastname from hrmresource where id =userid), COUNT(requestid) AS Expr1 FROM workflow_currentoperator WHERE workflowtype > 0 and (isremark IN ('0', '1', '5','8','9','7')) AND (islasttimes = 1) AND (usertype = 0)  and exists (select 1 from hrmresource where hrmresource.id=workflow_currentoperator.userid and hrmresource.status in (0,1,2,3) ) AND EXISTS (SELECT 1 FROM workflow_base WHERE id = workflow_currentoperator.workflowid AND (isvalid = '1'))  and exists (select 1 from workflow_requestbase where requestid = workflow_currentoperator.requestid and (deleted=0 or deleted is null)) GROUP BY userid ORDER BY COUNT(requestid) desc) where rownum<=10
• 登陆最少统计(oracle)：
  select relatedname,c from (select relatedname,COUNT(*) as c from SysMaintenanceLog where operateitem=60 group by relatedname order by c asc)where rownum<=5
• 待办事宜最多的12个人（oracle）：
  select lastname,c from (select (select lastname from hrmresource where id=userid) lastname ,count(requestid) as c from workflow_currentoperator where isremark in ('0','8','9') and userid>1 group by userid order by c desc)where rownum<=12