每人,每天,做好每件事
Everyone,Everyday,Everything

【泛微】Ecology常用SQL视图分享

本文中的sql语句,主要适用于Oracle数据库和Sqlserver数据库。其他数据库在部分sql语句写法上略有差异,可以参考此文的sql处理逻辑进行修改。

SQL视图说明

视图就是将一个或多个表的数据,通过sql语句关联查询出的一个虚拟的表。我们可以这样理解:

将数据库的select语句的结果,通过一张固定名称的表存起来,就是我们所谓的视图。

特别注意:视图并不是真实的表,数据都是“借”来的,只能“查”,不能直接针对视图进行“增删改”。

不同的数据库,有不同的sql写法:

说明:“–” 后为说明语句

Orace数据库创建和修改视图

create  or replace --创建或替换

view  --固定写法

view_shitumingcheng  --视图名称(视图名称已办都建议写成view_XXXX)

as --固定写法

select  ……--正常的selec语句

Sqlserver数据库创建和修改

create  --创建

view  --固定写法

view_shitumingcheng  --视图名称(视图名称已办都建议写成view_XXXX)

as --固定写法

select  ……--正常的selec语句
alter  --修改

view  --固定写法

view_shitumingcheng  --视图名称(视图名称已办都建议写成view_XXXX)

as --固定写法

select  ……--正常的selec语句

常用视图

无特别说明的情况下,sql语句同时适用于oracle和sqlserver数据库

以下仅列明查询语句,如需创建或修改视图可按照相关数据库规则进行调整

所有流程的待办view_alltodo

说明:一条流程可能在多个用户的账户中有待办(例如我们公司自己用的内部留言)以用户为维度,可根据用户查出本人所有待办流程

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

所有流程的待处理人view_dclrforallflow

以流程为维度,查询出本流程的所有待处理人

思路分析:将所有流程待办中userid按照requestid合并起来

注意:Oracle关于列转行的写法和sqlserver不一样,需要单独根据规则来写

--Sqlserver实现

SELECT

       requestid,

       (STUFF ((SELECT ',' + CAST(userid AS  varchar)  FROM

       view_alltodo WHERE requestid =  c.requestid FOR XML PATH ( '' ) ),1,1,'' ) ) as dclr--流程的全部待处理人

FROM

       view_alltodo c

GROUP  BY requestid;
--Oracle实现

select  requestid,

       LISTAGG(userid, ',') within group(order  by requestid) as dclr

  from view_alltodo

 --where requestid = 4225 –(where条件写在group by 前面)

 group by requestid

所有流程已办view_allybsy

以人为维度,找出每个人的已办记录

思路分析:每个流程可能一个人已办过多次,我们只需要取其中一次的已办记录(如果需要取处理时间,则取最后一次已办记录,最后一次已办记录=办理日期+时间最大的那一条),每个流程有多个已办,每个流程参与过的人,均有一条已办记录

select  row_number() OVER(ORDER BY b.requestid) AS id,--数据ID

       b.requestid, --流程ID

       a.requestmark, --流程编号

       b.workflowid, --流程ID

       a.creater, --创建人

       a.createdate, --创建日期

       d.departmentid, --部门

       b.userid, --处理人

       c.dclr --待处理人

  from (select distinct requestid, userid,  workflowid

          from workflow_currentoperator

         where isremark in ('2', '4')) b --所有流程已办

  left join workflow_requestbase a

    on b.requestid = a.requestid

  left join hrmresource d

    on a.creater = d.id

  left join view_dclrforallflow c --所有流程的待处理人

    on b.requestid = c.requestid

 where b.requestid = 39347--查询条件写在最后

所有我的请求view_allmyrequest

以人为维度,统计每个人发起的流程

思路分析:每个流程仅能由一个人发起,所以也可以视为以流程为维度统计每个流程都有一条唯一的workflow_requestbase表记录

create  or replace view view_allmyrequest as

select  a.requestid, --请求ID(流程)

       a.creater, --创建人(人力资源)

       a.requestname, --流程名称(文本)

       a.requestmark, --流程编号(文本)

       a.createdate, --创建日期(日期)

       b.dclr --待处理人(多人力)

  from workflow_requestbase a --流程基本信息表(每个请求一条记录

  left join view_dclrforallflow b --所有流程待处理人(每个请求一条记录)

    on a.requestid = b.requestid

 --where a.workflowid = 202 --条件写在最后

所有用户的待办数量

以用户为维度(此处以loginid为用户唯一识别,也可以直接用ID)

分析:需要考虑代理,子账号的情况

select  COUNT(*), e.loginid

  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

 group by e.loginid

多个流程表拼接

多个流程的拼接,常用于同一类流程因为实际需求被拆分成了多个流程,但是在用户统计数据时,希望合并起来统计的情况

格式:

Select  

a.字段1  as name1

a.字段2  as name2

a.字段3  as name3

0        as name 4

‘文本1’   as name 5

from  tablename a

where  a.字段1 = ‘xxx’

union  all

select  

b.字段1  as name1

b.字段2  as name2

b.字段3  as name3

1        as name 4

‘文本2’   as name 5

from  tablename a

where  b.字段1 = ‘xxx’
select

a.requestId  as requestid, --请求ID

m.requestname  as requestname,--请求标题

(case  when a.htmc = '' then m.requestname   else a.htmc end) as htmc,--合同名称

a.htbianh  as htbh,--合同编号

a.j  as sqbm,--申请部门

a.sqri  as sqrq,--申请日期

a.sfgeng  as sfgz,--是否跟踪(选择框-下拉框      是 否)

a.htz,  --合同金额

m.creater  as creater,--创建人

0  as htlx --合同类型

from  formtable_main_20 a--产品销售合同

left  join workflow_requestbase m on a.requestid = m.requestid where a.requestId is  not null

UNION  ALL

select

b.requestId  as requestid, --请求ID

n.requestname  as requestname,--请求标题

(case  when b.htmc = '' then n.requestname   else b.htmc end) as htmc,--合同名称

b.ht  as htbh,--合同编号

b.cgbum  as sqbm,--申请部门

b.sqrq  as sqrq,--申请日期

b.dangqianzhuangtai  as dangqianzhuangtai,--当前状态

b.sfgeng  as sfgz,--是否跟踪(选择框-下拉框      是 否)

b.htje  as htje, --合同金额

n.creater  as creater,--创建人

1  as htlx --合同类型

from  formtable_main_109 b  --非生产性采购合同

left  join workflow_requestbase n on b.requestid = n.requestid where b.requestId is  not null

子流程处理进度

以流程为维度,查看某流程子流程的情况

注意:此视图引用了view_dclrforallflow,必须优先创建好view_dclrforallflow视图

select  t1.requestid,--请求ID

       t1.mainrequestid,--主流程ID

       t1.requestname,--请求名称

       t1.creater,--创建人

       t1.currentnodeid,--节点ID

       t2.dclr--待处理人

  from (select a.requestid,

               b.mainrequestid,

               a.requestname,

               a.creater,

               a.currentnodeid

          from workflow_requestbase a

          left join workflow_subwfrequest b

            on a.requestid = b.subrequestid

         where b.mainrequestid > 0) t1

  left join view_dclrforallflow t2

    on t1.requestid = t2.requestid;
赞(1) 打赏
未经允许不得转载:辣椒小鱼のBlog » 【泛微】Ecology常用SQL视图分享
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

×
订阅图标按钮