{"id":246380,"date":"2020-12-18T19:48:31","date_gmt":"2020-12-18T11:48:31","guid":{"rendered":"http:\/\/4563.org\/?p=246380"},"modified":"2020-12-18T19:48:31","modified_gmt":"2020-12-18T11:48:31","slug":"%e8%af%b7%e9%97%ae-%e5%a4%a7%e4%bd%ac%e4%bb%ac%e8%bf%99%e4%b8%aa%e6%ae%b5-sql-%e6%80%8e%e4%b9%88%e4%bc%98%e5%8c%96%ef%bc%9f%e6%80%a5","status":"publish","type":"post","link":"http:\/\/4563.org\/?p=246380","title":{"rendered":"\u8bf7\u95ee \u5927\u4f6c\u4eec\u8fd9\u4e2a\u6bb5 sql \u600e\u4e48\u4f18\u5316\uff1f\u6025"},"content":{"rendered":"<div>\n<div>\n<div>\n<h1>                  \u8bf7\u95ee \u5927\u4f6c\u4eec\u8fd9\u4e2a\u6bb5 sql \u600e\u4e48\u4f18\u5316\uff1f\u6025               <\/h1>\n<p> <\/p>\n<div>\n<div> <span>\u8cc7\u6df1\u5927\u4f6c : hemingyang <\/span>  <span><i><\/i> 4<\/span> <\/div>\n<div> <\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<div isfirst=\"1\"> <\/p>\n<p>select distinct RE.* from ( SELECT R.ID_ id, to_number(T.PROC_INST_ID_) processInstanceId, Q.TEXT_ busiType, P.NAME_ definitionName, TO_DATE(TO_CHAR(T.START_TIME_, &#8216;YYYY-MM-DD HH24:MI:SS&#8217;), &#8216;YYYY-MM-DD HH24:MI:SS&#8217;) createTime, DECODE(NVL(L.PROC_INST_ID_, 0), 0, TO_DATE(null, &#8216;YYYY-MM-DD&#8217;), TO_DATE(TO_CHAR(T.END_TIME_, &#8216;YYYY-MM-DD HH24:MI:SS&#8217;), &#8216;YYYY-MM-DD HH24:MI:SS&#8217;)) endTime, R.NAME_ curName, ( SELECT USERNAME || &#8216;(&#8216; || REALNAME || &#8216;)&#8217; FROM t_System_User WHERE id = T.START_USER_ID_ ) AS startUserId, (SELECT ur.phone from T_SYSTEM_USER ur where ur.id=T.START_USER_ID_) as sqrlxdh, ( SELECT u1.DEPARTMENT FROM t_System_User u1 WHERE id = T.START_USER_ID_ ) AS startUserXx, (SELECT TK.ASSIGNEE_ FROM ACT_RU_TASK TK WHERE TK.PROC_INST_ID_ = T.PROC_INST_ID_ AND TK.ASSIGNEE_ IS NOT NULL) assignee, (SELECT UR.REALNAME FROM T_SYSTEM_USER UR WHERE UR.ID = (SELECT TK.ASSIGNEE_ FROM ACT_RU_TASK TK WHERE TK.PROC_INST_ID_ = T.PROC_INST_ID_ AND TK.ASSIGNEE_ IS NOT NULL)) assigneeName, (SELECT ur.phone from T_SYSTEM_USER ur where ur.id=(SELECT TK.ASSIGNEE_ FROM ACT_RU_TASK TK WHERE TK.PROC_INST_ID_ = T.PROC_INST_ID_ AND TK.ASSIGNEE_ IS NOT NULL)) as jsrlxdh FROM ACT_HI_PROCINST T, ACT_HI_VARINST Q, ACT_RE_PROCDEF P, ACT_HI_ACTINST L, (SELECT T1.*, ROW_NUMBER() OVER(PARTITION BY T1.PROC_INST_ID_ ORDER BY T1.START_TIME_ DESC NULLS LAST) RN FROM ACT_HI_TASKINST T1) R, T_SYSTEM_USER U WHERE T.PROC_INST_ID_ = R.PROC_INST_ID_ AND T.PROC_INST_ID_ = Q.PROC_INST_ID_ AND Q.NAME_ = &#8216;busiType&#8217; AND P.ID_ = T.PROC_DEF_ID_ AND T.PROC_INST_ID_ = L.PROC_INST_ID_(+) AND T.START_USER_ID_ = U.ID AND L.ACT_TYPE_(+) = &#8216;endEvent&#8217; AND R.RN = &#8216;1&#8217; AND EXISTS (SELECT 1 FROM ACT_HI_TASKINST Q WHERE Q.PROC_INST_ID_ = T.PROC_INST_ID_ AND Q.ASSIGNEE_ = &#8216;abebf5330c71440ab6a4719e535b8129&#8217;) and not exists ( select 1 from ACT_RU_TASK RES LEFT JOIN ACT_RU_IDENTITYLINK I ON I.TASK_ID_ = RES.ID_ LEFT JOIN ACT_RU_EXECUTION REX ON REX.PROC_INST_ID_ = RES.PROC_INST_ID_ WHERE RES.SUSPENSION_STATE_ = 1 AND REX.PARENT_ID_ IS NULL and res.ID_ = r.ID_ AND ( RES.ASSIGNEE_ = &#8216;abebf5330c71440ab6a4719e535b8129&#8217; OR ( RES.ASSIGNEE_ IS NULL AND I.TYPE_ = &#8216;candidate&#8217; AND ( I.USER_ID_ = &#8216;abebf5330c71440ab6a4719e535b8129&#8217; OR I.GROUP_ID_ IN ( SELECT g.ID_ FROM ACT_ID_GROUP g, ACT_ID_MEMBERSHIP membership WHERE g.ID_ = membership.GROUP_ID_ AND membership.USER_ID_ = &#8216;abebf5330c71440ab6a4719e535b8129&#8217; )) ) ) )<\/p>\n<pre><code> ) RE   ORDER BY endTime DESC, createTime DESC <\/code><\/pre>\n<\/p><\/div>\n<div> <b>\u5927\u4f6c\u6709\u8a71\u8aaa<\/b> (<span>9<\/span>)        <\/div>\n<div> <\/div>\n<\/p><\/div>\n<\/p><\/div>\n<ul>\n<li data-pid=\"4801714\" data-uid=\"2\">\n<div>\n<div>\n<div> <span>\u8cc7\u6df1\u5927\u4f6c : l00t <\/span>  <\/div>\n<div> <i title=\"\u5f15\u7528\"><\/i>  <span>          <\/span> <\/div>\n<\/p><\/div>\n<div>                                                             \u4f60\u8fd9\u53d1\u7ed9\u8c01\u770b\uff1f\u4e3a\u4ec0\u4e48\u8981\u4f18\u5316\uff1f\u8981\u4f18\u5316\u6210\u4ec0\u4e48\u6837\uff1f                                                            <\/div>\n<\/p><\/div>\n<\/li>\n<li data-pid=\"4801715\" data-uid=\"2\">\n<div>\n<div>\n<div> <span>\u8cc7\u6df1\u5927\u4f6c : npe <\/span>  <\/div>\n<div> <i title=\"\u5f15\u7528\"><\/i>  <span>          <\/span> <\/div>\n<\/p><\/div>\n<div>                                                             truncate table                                                            <\/div>\n<\/p><\/div>\n<\/li>\n<li data-pid=\"4801716\" data-uid=\"2\">\n<div>\n<div>\n<div> <span>\u8cc7\u6df1\u5927\u4f6c : YYYYMMDDHHSS <\/span>  <\/div>\n<div> <i title=\"\u5f15\u7528\"><\/i>  <span>          <\/span> <\/div>\n<\/p><\/div>\n<div>                                                             \u6839\u672c\u4e0d\u662f\u4f1a\u5199 sql \u7684\u4eba\u5199\u5f97\u51fa\u6765\u7684\u6837\u5b50\uff0csqlboy \u5982\u662f\u8bf4                                                            <\/div>\n<\/p><\/div>\n<\/li>\n<li data-pid=\"4801717\" data-uid=\"2\">\n<div>\n<div>\n<div> <span>\u4e3b<\/span> <span>\u8cc7\u6df1\u5927\u4f6c : hemingyang <\/span>  <\/div>\n<div> <i title=\"\u5f15\u7528\"><\/i>  <span>          <\/span> <\/div>\n<\/p><\/div>\n<div>                                                             @YYYYMMDDHHSS \u5927\u4f6c\u6709\u65b9\u6848\u5417                                                            <\/div>\n<\/p><\/div>\n<\/li>\n<li data-pid=\"4801718\" data-uid=\"2\">\n<div>\n<div>\n<div> <span>\u4e3b<\/span> <span>\u8cc7\u6df1\u5927\u4f6c : hemingyang <\/span>  <\/div>\n<div> <i title=\"\u5f15\u7528\"><\/i>  <span>          <\/span> <\/div>\n<\/p><\/div>\n<div>                                                             &lt;h1&gt;<\/p>\n<p>select distinct RE.* from (<br \/> SELECT R.ID_ id,<br \/> to_number(T.PROC_INST_ID_) processInstanceId,<br \/> Q.TEXT_ busiType,<br \/> P.NAME_ definitionName,<br \/> TO_DATE(TO_CHAR(T.START_TIME_, &#8216;YYYY-MM-DD HH24:MI:SS&#8217;),<br \/> &#8216;YYYY-MM-DD HH24:MI:SS&#8217;) createTime,<br \/> DECODE(NVL(L.PROC_INST_ID_, 0),<br \/> 0,<br \/> TO_DATE(null, &#8216;YYYY-MM-DD&#8217;),<br \/> TO_DATE(TO_CHAR(T.END_TIME_, &#8216;YYYY-MM-DD HH24:MI:SS&#8217;),<br \/> &#8216;YYYY-MM-DD HH24:MI:SS&#8217;)) endTime,<br \/> R.NAME_ curName,<br \/> ( SELECT USERNAME || &#8216;(&#8216; || REALNAME || &#8216;)&#8217; FROM t_System_User WHERE id = T.START_USER_ID_ ) AS startUserId,<br \/> (SELECT ur.phone from T_SYSTEM_USER ur where ur.id=T.START_USER_ID_) as sqrlxdh,<br \/> ( SELECT u1.DEPARTMENT FROM t_System_User u1 WHERE id = T.START_USER_ID_ ) AS startUserXx,<br \/> (SELECT TK.ASSIGNEE_<br \/> FROM ACT_RU_TASK TK<br \/> WHERE TK.PROC_INST_ID_ = T.PROC_INST_ID_<br \/> AND TK.ASSIGNEE_ IS NOT NULL) assignee,<br \/> (SELECT UR.REALNAME<br \/> FROM T_SYSTEM_USER UR<br \/> WHERE UR.ID =<br \/> (SELECT TK.ASSIGNEE_<br \/> FROM ACT_RU_TASK TK<br \/> WHERE TK.PROC_INST_ID_ = T.PROC_INST_ID_<br \/> AND TK.ASSIGNEE_ IS NOT NULL)) assigneeName,<br \/> (SELECT ur.phone from T_SYSTEM_USER ur where ur.id=(SELECT TK.ASSIGNEE_<br \/> FROM ACT_RU_TASK TK<br \/> WHERE TK.PROC_INST_ID_ = T.PROC_INST_ID_<br \/> AND TK.ASSIGNEE_ IS NOT NULL)) as jsrlxdh<br \/> FROM ACT_HI_PROCINST T,<br \/> ACT_HI_VARINST Q,<br \/> ACT_RE_PROCDEF P,<br \/> ACT_HI_ACTINST L,<br \/> (SELECT T1.*,<br \/> ROW_NUMBER() OVER(PARTITION BY T1.PROC_INST_ID_ ORDER BY T1.START_TIME_ DESC NULLS LAST) RN<br \/> FROM ACT_HI_TASKINST T1) R,<br \/> (SELECT u.id FROM ACT_HI_PROCINST T JOIN T_SYSTEM_USER U ON T.START_USER_ID_ = U.Id ) tr<br \/> WHERE T.PROC_INST_ID_ = R.PROC_INST_ID_<br \/> AND T.PROC_INST_ID_ = Q.PROC_INST_ID_<br \/> AND Q.NAME_ = &#8216;busiType&#8217;<br \/> AND P.ID_ = T.PROC_DEF_ID_<br \/> AND T.PROC_INST_ID_ = L.PROC_INST_ID_(+)<br \/> AND L.ACT_TYPE_(+) = &#8216;endEvent&#8217;<br \/> AND T.START_USER_ID_ = tr.Id<br \/> AND R.RN = &#8216;1&#8217;<br \/> AND EXISTS (SELECT 1<br \/> FROM ACT_HI_TASKINST Q<br \/> WHERE Q.PROC_INST_ID_ = T.PROC_INST_ID_<br \/> AND Q.ASSIGNEE_ = &#8216;abebf5330c71440ab6a4719e535b8129&#8217;)<br \/> and not exists (<br \/> select 1 from<br \/> ACT_RU_TASK RES<br \/> LEFT JOIN ACT_RU_IDENTITYLINK I ON I.TASK_ID_ = RES.ID_<br \/> LEFT JOIN ACT_RU_EXECUTION REX ON REX.PROC_INST_ID_ = RES.PROC_INST_ID_<br \/> WHERE RES.SUSPENSION_STATE_ = 1<br \/> AND REX.PARENT_ID_ IS NULL<br \/> and res.ID_ = r.ID_<br \/> AND (<br \/> RES.ASSIGNEE_ = &#8216;abebf5330c71440ab6a4719e535b8129&#8217;<br \/> OR (<br \/> RES.ASSIGNEE_ IS NULL<br \/> AND I.TYPE_ = &#8216;candidate&#8217;<br \/> AND ( I.USER_ID_ = &#8216;abebf5330c71440ab6a4719e535b8129&#8217; OR I.GROUP_ID_ IN ( SELECT g.ID_ FROM ACT_ID_GROUP g, ACT_ID_MEMBERSHIP membership WHERE g.ID_ = membership.GROUP_ID_ AND membership.USER_ID_ = &#8216;abebf5330c71440ab6a4719e535b8129&#8217; ))<br \/> )<br \/> )<br \/> )<\/p>\n<p> ) RE<br \/> ORDER BY endTime DESC, createTime DESC<\/p>\n<p>&lt;\/h1&gt;                                                            <\/p><\/div>\n<\/p><\/div>\n<\/li>\n<li data-pid=\"4801719\" data-uid=\"2\">\n<div>\n<div>\n<div> <span>\u8cc7\u6df1\u5927\u4f6c : totoro52 <\/span>  <\/div>\n<div> <i title=\"\u5f15\u7528\"><\/i>  <span>          <\/span> <\/div>\n<\/p><\/div>\n<div>                                                             \u76f4\u63a5\u7529\u4e00\u6761 SQL \u51fa\u6765\u8ba9\u4eba\u600e\u4e48\u4f18\u5316\u3002\u3002\u3002                                                            <\/div>\n<\/p><\/div>\n<\/li>\n<li data-pid=\"4801720\" data-uid=\"2\">\n<div>\n<div>\n<div> <span>\u4e3b<\/span> <span>\u8cc7\u6df1\u5927\u4f6c : hemingyang <\/span>  <\/div>\n<div> <i title=\"\u5f15\u7528\"><\/i>  <span>          <\/span> <\/div>\n<\/p><\/div>\n<div>                                                             @totoro52 \u5c31\u662f T_SYSTEM_USER \u8868\u6570\u636e\u5f88\u5927 \u9700\u8981\u5173\u8054 ACT_HI_PROCINST \u5c31\u4f1a\u5f88\u6162                                                            <\/div>\n<\/p><\/div>\n<\/li>\n<li data-pid=\"4801721\" data-uid=\"2\">\n<div>\n<div>\n<div> <span>\u8cc7\u6df1\u5927\u4f6c : NotFoundEgg <\/span>  <\/div>\n<div> <i title=\"\u5f15\u7528\"><\/i>  <span>          <\/span> <\/div>\n<\/p><\/div>\n<div>                                                             \u4e00\u770b\u5230 activiti \u7684\u5f15\u64ce\u8868\u5c31\u5934\u75bc <\/p>\n<p>\u4e5f\u4e0d\u8bf4\u8fd9\u662f\u5e72\u5565\u7684                                                            <\/p><\/div>\n<\/p><\/div>\n<\/li>\n<li data-pid=\"4801722\" data-uid=\"2\">\n<div>\n<div>\n<div> <span>\u8cc7\u6df1\u5927\u4f6c : jtwor <\/span>  <\/div>\n<div> <i title=\"\u5f15\u7528\"><\/i>  <span>          <\/span> <\/div>\n<\/p><\/div>\n<div>                                                             \u5b50\u67e5\u8be2\u592a\u591a\u4e86 \u53ef\u4ee5\u5173\u8054\u7684 \u4e0d\u8981\u7528 in \u7528 exists                                                            <\/div>\n<\/p><\/div>\n<\/li>\n<li>\n","protected":false},"excerpt":{"rendered":"<p>\u8bf7\u95ee \u5927\u4f6c\u4eec\u8fd9\u4e2a\u6bb5 sql \u600e\u4e48\u4f18&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[],"tags":[],"_links":{"self":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/246380"}],"collection":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=246380"}],"version-history":[{"count":0,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/246380\/revisions"}],"wp:attachment":[{"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=246380"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=246380"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=246380"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}