- 登陆最少统计:
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
泛微常用sql语句
于 2024-08-30 14:40:13 首次发布