关于数据查询

 

 

这是一张工作流的历史记录表，他保存了每一个流程实例的所有的历史步骤，其中某些地方不符合范式是因为未了提升查询效率，避免多表查询，如流程实例和流程定义ID都可以在此表中找到；

CREATE TABLE xwworkflowprocesshistory ( xwworkflowprocesshistoryid uuid NOT NULL, -- 流程操作历史ID xwworkflowid uuid NOT NULL, -- 流程定义ID xwexecutorid integer NOT NULL, -- 参与人 xwexecutetime timestamp without time zone NOT NULL, -- 操作时间 xwworkflowprocessid uuid NOT NULL, --流程实例ID xwcurrentstepid character varying(50) NOT NULL, -- 当前操作步骤ID CONSTRAINT pk_xwworkflowprocesshistory PRIMARY KEY (xwworkflowprocesshistoryid) )

 

1）按照时间降序查找某个员工所参与的所有的流程实例，如果一个员工两次参与了一个流程，则只需找出最近参与的历史记录：

 

--查找出某个人所参与的所有事务信息，不要求重复 select a.* from xwworkflowprocesshistory a ,(SELECT xwexecutorid,xwworkflowprocessid,max(xwexecutetime) as maxtime FROM xwworkflowprocesshistory group by xwexecutorid,xwworkflowprocessid,xwworkflowid,xwenterprisenumber having xwexecutorid=10000403 and xwworkflowid='2d72c7bb-2350-4fc2-8981-20b80cfedac5' and xwenterprisenumber=1000192) b where a.xwexecutorid=b.xwexecutorid and a.xwworkflowprocessid=b.xwworkflowprocessid and a.xwexecutetime=b.maxtime order by a.xwexecutetime

 

 

2）查找出参与事务节点最密集的人（就是参与事务实例最多的人）的历史记录

 

--参与事务最多的人所有事务历史信息 select * from xwworkflowprocesshistory where xwexecutorid=(select xwexecutorid from xwworkflowprocesshistory group by xwexecutorid having count(*)=(select max(c) from (select count(*) as c,xwexecutorid from xwworkflowprocesshistory group by xwexecutorid) as sub))

 

3）查找出参与人最多（不含重复）的流程实例：

--select xwexecutorid,xwworkflowprocessid from xwworkflowprocesshistory group by xwworkflowprocessid,xwexecutorid --select count(*) as c,xwworkflowprocessid from (select xwexecutorid,xwworkflowprocessid from xwworkflowprocesshistory group by xwworkflowprocessid,xwexecutorid) as A group by xwworkflowprocessid --select max(c) from (select count(*) as c,xwworkflowprocessid from (select xwexecutorid,xwworkflowprocessid from xwworkflowprocesshistory group by xwworkflowprocessid,xwexecutorid) as A group by xwworkflowprocessid ) as B select count(*) as c,xwworkflowprocessid from (select xwexecutorid,xwworkflowprocessid from xwworkflowprocesshistory group by xwworkflowprocessid,xwexecutorid) as A group by xwworkflowprocessid having count(*)=(select max(c) from (select count(*) as c,xwworkflowprocessid from (select xwexecutorid,xwworkflowprocessid from xwworkflowprocesshistory group by xwworkflowprocessid,xwexecutorid) as A group by xwworkflowprocessid ) as B )