阳光网驿-企业信息化交流平台【DTC零售连锁全渠道解决方案】

 找回密码
 注册

QQ登录

只需一步,快速开始

扫描二维码登录本站

手机号码,快捷登录

老司机
查看: 1226|回复: 1

[推荐] 分享几个很实用的SQL,拿来就能用

[复制链接]
  • TA的每日心情
    开心
    2017-11-21 22:24
  • 签到天数: 10 天

    [LV.3]偶尔看看II

    发表于 2017-11-9 23:30:36 | 显示全部楼层 |阅读模式
    使用门户的图表元素
    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




    泛微Ecology oa 协同办公,流程、解决方案
    扫描二维码关注“泛微Ecology”,每天送上新鲜大餐


    楼主热帖
    启用邀请码注册,提高发帖质量,建设交流社区
  • TA的每日心情
    开心
    2018-10-23 02:28
  • 签到天数: 1 天

    [LV.1]初来乍到

    发表于 2018-10-23 03:32:19 | 显示全部楼层
    泛微OA助力格力电器打造集成统一的精细化管理
    启用邀请码注册,提高发帖质量,建设交流社区
    您需要登录后才可以回帖 登录 | 注册

    本版积分规则

    快速回复 返回顶部 返回列表