本文已参与「新人创作礼」活动，一起开启掘金创作之路。

涉及流程管理相关的SQL语句(五)

查询每个人的待办事宜汇总数据

select COUNT(*), e.loginid,e.lastname
  from (select t2.requestid,
               (case
                 when c.belongto > 0 then
                  c.belongto
                 else
                  t2.userid
               end) newuserid
          from workflow_requestbase     t1,
               workflow_currentoperator t2,
               hrmresource              c
         where (t1.deleted <> 1 or t1.deleted is null or t1.deleted = '')
           and t1.requestid = t2.requestid
           and t2.userid = c.id
           and t2.usertype = 0
           and (t1.deleted = 0 or t1.deleted is null)
           and ((t2.isremark = '0' and
               (t2.takisremark is null or t2.takisremark = 0)) or
               t2.isremark in ('1', '5', '8', '9', '7'))
           and (t1.deleted = 0 or t1.deleted is null)
           and t2.islasttimes = 1
          -- and (nvl(t1.currentstatus, -1) = -1 or
          --(nvl(t1.currentstatus, -1) = 0 and t1.creater in (1)))
           and t1.workflowid in
              (select id
                  from workflow_base
                 where (isvalid = '1' or isvalid = '3'))) d,
       hrmresource e
 where d.newuserid = e.id 
 --and e.lastname='烧硬盘'
 group by e.loginid,e.lastname

查询指定某人的待办流程详情

--查待办sql(oracle, sqlserver下isnull函数用isnull替换)
select distinct
    t1.requestid
  , t1.requestmark
  , t1.createdate
  , t1.createtime
  , t1.creater
  , t1.creatertype
  , t1.workflowid
  , t1.requestname
  , t1.requestnamenew
  , t1.status
  , t1.requestlevel
  , t1.currentnodeid
  , t2.viewtype
  , t2.userid
  , t2.receivedate
  , t2.receivetime
  , t2.isremark
  , t2.nodeid
  , t2.agentorbyagentid
  , t2.agenttype
  , t2.isprocessed
from
    workflow_requestbase     t1
  , workflow_currentoperator t2
where
    (
        t1.deleted         <> 1
        or t1.deleted is null
        or t1.deleted       = ''
    )
    and t1.requestid = t2.requestid
    and t2.userid    = 用户ID
    and t2.usertype  = 0
    and
    (
        t1.deleted          = 0
        or t1.deleted is null
    )
    and
    (
        (
            t2.isremark = '0'
            and
            (
                t2.takisremark is null
                or t2.takisremark    = 0
            )
        )
        or t2.isremark in ('1'
                         , '5'
                         , '8'
                         , '9'
                         , '7')
    )
    and
    (
        t1.deleted          = 0
        or t1.deleted is null
    )
    and t2.islasttimes = 1
    and
    (
        isnull(t1.currentstatus, -1) = -1
        or
        (
            isnull(t1.currentstatus, -1) = 0
            and t1.creater in (用户ID)
        )
    )
    and t1.workflowid in
    (
        select
            id
        from
            workflow_base
        where
            (
                isvalid    = '1'
                or isvalid = '3'
            )
    )
order by
    t2.receivedate desc
  , t2.receivetime desc
  , t1.requestid desc

查询所有已办结事宜的sql

--所有待办事宜汇总
select distinct
row_number() OVER (ORDER BY c.requestid) AS id,--根据请求ID排序的序号生成ID
requestid ,--流程ID
userid,--用户ID
workflowid --流程ID
from 
workflow_currentoperator  c  --流程当前操作人表
where
((c.isremark = '0' 
   and (c.takisremark is null or c.takisremark = 0)) 
   or c.isremark in ('1', '5', '8', '9', '7'))
and c.islasttimes = 1
--and userid=3310

待办事宜数量前5机构统计

select top 5
    (
        select
            subcompanyname
        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
    b.lastname 姓名
  , c.workflowname 流程类型
  , e.requestname 请求标题
  , d.nodename 节点
  , a.receivedate 接收日期
  , a.receivetime 接收时间
  , a.operatedate 处理日期
  , a.operatetime 处理时间
  , DATEDIFF(mi,receivedate + ' ' + receivetime,operatedate + ' ' + operatetime) as '耗时/分钟'
from
    workflow_currentoperator a
    left JOIN
        hrmresource b
        on
            a.userid = b.id
    left join
        workflow_base c
        on
            a.workflowid = c.id
    left join
        workflow_nodebase d
        on
            a.nodeid = d.id
    left join
        workflow_requestbase e
        on
            a.requestid = e.requestid
where
    a.operatedate     is not null
    and a.operatetime is not null
    and a.isremark in (2, 4)
    and a.receivedate >= '2022-01-01'
    and a.receivedate <= '2022-08-29' --时间范围控制
    and a.userid       = 3            --人员控制
order by
    a.id asc