{"id":94,"date":"2015-01-13T11:14:00","date_gmt":"2015-01-13T03:14:00","guid":{"rendered":"http:\/\/www.llku.com\/llku\/?p=94"},"modified":"2015-06-26T09:07:31","modified_gmt":"2015-06-26T01:07:31","slug":"%e8%8e%b7%e5%8f%96%e8%b4%a2%e5%8a%a1%e7%b3%bb%e7%bb%9f%e8%ae%be%e7%bd%ae%e5%ae%a1%e6%89%b9%e9%93%be%e7%9b%b8%e5%85%b3%e4%bf%a1%e6%81%afsql","status":"publish","type":"post","link":"https:\/\/www.llku.com\/?p=94","title":{"rendered":"\u83b7\u53d6\u8d22\u52a1\u7cfb\u7edf\u8bbe\u7f6e\u5ba1\u6279\u94fe\u76f8\u5173\u4fe1\u606fSQL"},"content":{"rendered":"<p>\n\tSELECT DISTINCT PS.POSITION_STRUCTURE_ID&nbsp;&nbsp;&nbsp; \u5ba1\u6279\u94feID,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PS.NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \u5ba1\u6279\u94fe\u540d\u79f0,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PE.POS_STRUCTURE_ELEMENT_ID POS_STRUCTURE_ELEMENT_ID,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; HP.POSITION_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \u804c\u4f4dID,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; HP.NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \u804c\u4f4d\u540d,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PPF.PERSON_ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \u5458\u5de5ID,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PPF.LAST_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; \u7528\u6237\u540d,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PE.ORDER_FLAG&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ORDER_FLAG<br \/>\n\t&nbsp; FROM PER_POS_STRUCTURE_VERSIONS PV,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PER_POSITION_STRUCTURES PS,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (SELECT POS_STRUCTURE_VERSION_ID,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; POS_STRUCTURE_ELEMENT_ID,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PARENT_POSITION_ID,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ORDER_FLAG<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM (SELECT PE.POS_STRUCTURE_VERSION_ID,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PE.POS_STRUCTURE_ELEMENT_ID,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PE.PARENT_POSITION_ID,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39;1&#39; ORDER_FLAG<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM PER_POS_STRUCTURE_ELEMENTS PE<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UNION ALL<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT PE.POS_STRUCTURE_VERSION_ID,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PE.POS_STRUCTURE_ELEMENT_ID,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PE.SUBORDINATE_POSITION_ID PARENT_POSITION_ID,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39;2&#39; ORDER_FLAG<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM PER_POS_STRUCTURE_ELEMENTS PE<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE NOT EXISTS (SELECT 1<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM PER_POS_STRUCTURE_ELEMENTS PE2<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE PE2.POS_STRUCTURE_VERSION_ID =<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PE.POS_STRUCTURE_VERSION_ID<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND PE.SUBORDINATE_POSITION_ID =<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PE2.PARENT_POSITION_ID))) PE,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PER_PEOPLE_F PPF,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PER_ALL_ASSIGNMENTS_F PAF,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; HR_POSITIONS_F HP<br \/>\n\t&nbsp;WHERE PV.POS_STRUCTURE_VERSION_ID = PE.POS_STRUCTURE_VERSION_ID<br \/>\n\t&nbsp;&nbsp; AND PV.POSITION_STRUCTURE_ID = PS.POSITION_STRUCTURE_ID<br \/>\n\t&nbsp;&nbsp; AND PPF.PERSON_ID = PAF.PERSON_ID<br \/>\n\t&nbsp;&nbsp; AND PE.PARENT_POSITION_ID = HP.POSITION_ID<br \/>\n\t&nbsp;&nbsp; AND HP.POSITION_ID = PAF.POSITION_ID<br \/>\n\t&nbsp;&nbsp; AND (PAF.EFFECTIVE_END_DATE IS NULL OR PAF.EFFECTIVE_END_DATE &gt; SYSDATE)<br \/>\n\t&nbsp;&nbsp; AND (HP.EFFECTIVE_END_DATE IS NULL OR HP.EFFECTIVE_END_DATE &gt; SYSDATE)<br \/>\n\t&nbsp;&nbsp; AND (PPF.EFFECTIVE_END_DATE IS NULL OR PPF.EFFECTIVE_END_DATE &gt; SYSDATE)<br \/>\n\t&nbsp;&nbsp; AND PAF.EFFECTIVE_END_DATE &gt;= SYSDATE<br \/>\n\t&nbsp;&nbsp; &#8211;AND PS.NAME = &#39;000100\u9884\u7b97\u7533\u8bf7\u5ba1\u6279\u5c42\u6b21&#39;<br \/>\n\t&nbsp;ORDER BY PS.POSITION_STRUCTURE_ID,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PE.POS_STRUCTURE_ELEMENT_ID,<br \/>\n\t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PE.ORDER_FLAG;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>SELECT DISTINCT PS.POSITION_STRUCTURE_ID&nbsp;&nbsp;&#038;nb &hellip; <a href=\"https:\/\/www.llku.com\/?p=94\" class=\"more-link\">\u7ee7\u7eed\u9605\u8bfb<span class=\"screen-reader-text\">\u83b7\u53d6\u8d22\u52a1\u7cfb\u7edf\u8bbe\u7f6e\u5ba1\u6279\u94fe\u76f8\u5173\u4fe1\u606fSQL<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[14],"tags":[],"_links":{"self":[{"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/posts\/94"}],"collection":[{"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.llku.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=94"}],"version-history":[{"count":3,"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/posts\/94\/revisions"}],"predecessor-version":[{"id":543,"href":"https:\/\/www.llku.com\/index.php?rest_route=\/wp\/v2\/posts\/94\/revisions\/543"}],"wp:attachment":[{"href":"https:\/\/www.llku.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=94"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.llku.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=94"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.llku.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=94"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}