戳上面的蓝字“泛微Ecology”,关注/置顶我哦!
适用范围
本文中的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; |
泛微Ecology oa 协同办公,流程、解决方案
扫描二维码关注“泛微Ecology”,每天送上新鲜大餐。