【泛微】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;
THE END
0
二维码
打赏
海报
【泛微】Ecology常用SQL视图分享
本文中的sql语句，主要适用于Oracle数据库和Sqlserver数据库。其他数据库在部分sql语句写法上略有差异，可以参考此文的sql处理逻辑进行修改。
SQL视图说明……
