学步园

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

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

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

--查找出某个人所参与的所有事务信息，不要求重复<br /> select a.* from xwworkflowprocesshistory a ,(SELECT xwexecutorid,xwworkflowprocessid,max(xwexecutetime) as maxtime<br /> 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<br />

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

--参与事务最多的人所有事务历史信息<br /> 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))<br />

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

--select xwexecutorid,xwworkflowprocessid from xwworkflowprocesshistory group by xwworkflowprocessid,xwexecutorid</p> <p>--select count(*) as c,xwworkflowprocessid from (select xwexecutorid,xwworkflowprocessid from xwworkflowprocesshistory group by xwworkflowprocessid,xwexecutorid) as A group by xwworkflowprocessid</p> <p>--select max(c) from (select count(*) as c,xwworkflowprocessid from (select xwexecutorid,xwworkflowprocessid from xwworkflowprocesshistory group by xwworkflowprocessid,xwexecutorid) as A group by xwworkflowprocessid<br /> ) as B </p> <p>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<br /> ) as B )