【泛微OA】常用的门户图表元素sql

1、流程操作次数（条形图）

oracle:select * from (select (select lastname from hrmresource where id =userid),count(*) as 流程操作人次数 from workflow_currentoperator group by userid order by count(*) desc)where rownum<=10
sqlserver:select top 10* from (select (select lastname from hrmresource where id =userid),count(*) as 流程操作人次数 from workflow_currentoperator group by userid order by count(*) desc)

2、各单位人数比（饼图 3D）

oracle:select * from (select(select subcompanyname from hrmsubcompany where id=subCompanyId1),count(id) As cids from hrmresource group by subCompanyId1 order by cids desc)where rownum<=10
sqlserver:select top 10* from (select(select subcompanyname from hrmsubcompany where  id=subCompanyId1),count(id)  As  cids from  hrmresource  group  by subCompanyId1 order by cids desc)

3、登陆次数排名（面积图 '2012-07-01' 到 '2012-08-20'之间的）

oracle:select * from (select relatedname,count(*) as 系 统 登 陆 次 数 from
SysMaintenanceLog where operatetype = 6 and operatedate between '2012-07-01' and '2012-08-20' group by relatedid,relatedname order by count(*) desc) where rownum<=10
sqlserver:select top 10* from (select relatedname,count(*) as 系统登陆次数 from SysMaintenanceLog where operatetype = 6 and operatedate between '2012-07-01' and  '2012-08-20' group by relatedid,relatedname order by count(*) desc)

4、发起流程数排名（柱形图 3D）

oracle:select * from (select (select lastname from hrmresource where id = creater) as 用户,count(*) as 发起流程次数 from workflow_requestbase group by creater order by count(*) desc)where rownum<=10
sqlserver:select top 10* from (select (select lastname from hrmresource where id =creater) as 用户,count(*) as 发起流程次数 from workflow_requestbase group by creater order by count(*) desc)

5、性别比（圆盘）

oracle:select case sex when '0' then '男' else '女' end, count(id) as cid from
hrmresource group by sex
sqlserver:select case sex when '0' then '男' else '女' end, count(id) as cid from
hrmresource group by sex

6、文档贡献度排名

oracle ： Select *  from  ((select  lastname  from  hrmresource  where
id=doccreaterid) ,count(id) as cid from docdetail group by doccreaterid order by cid desc) where rownum<=10
sqlserver ： Select top  10  (select  lastname  from  hrmresource  where
id=doccreaterid) ,count(id) as cid from docdetail group by doccreaterid order by cid des