{"id":808,"date":"2023-07-14T11:32:43","date_gmt":"2023-07-14T03:32:43","guid":{"rendered":"https:\/\/www.furyblog.com\/?p=808"},"modified":"2023-07-14T11:32:44","modified_gmt":"2023-07-14T03:32:44","slug":"%e6%9f%a5%e8%af%a2%e6%b3%9b%e5%be%aeoa%e4%b8%ad%e5%ae%8c%e6%95%b4%e7%9a%84%e7%bb%84%e7%bb%87%e6%9e%b6%e6%9e%84","status":"publish","type":"post","link":"https:\/\/www.furyblog.com\/?p=808","title":{"rendered":"\u67e5\u8be2\u6cdb\u5faeOA\u4e2d\u5b8c\u6574\u7684\u7ec4\u7ec7\u67b6\u6784"},"content":{"rendered":"\n<p>\u5f88\u591a\u65f6\u5019\u8981\u7528\u5230\u7ec4\u7ec7\u67b6\u6784\uff0c\u6bd4\u5982\u77e9\u9635\u7f16\u8f91\u3001\u884c\u653f\u53d1\u6587\u7b49\uff0c\u6211\u4eec\u53ef\u4ee5\u76f4\u63a5\u4ece\u6570\u636e\u5e93\u4e2d\u67e5\u8be2\u83b7\u5f97\uff0c\u6cdb\u5fae\u5177\u6709\u516c\u53f8\u3001\u5206\u90e8\u3001\u90e8\u95e83\u4e2a\u7ef4\u5ea6\uff0c\u5728\u8be5SQL\u4e2d\uff0c\u4e0d\u540c\u7ef4\u5ea6\u4f7f\u7528||\u7b26\u53f7\u8fdb\u884c\u8fde\u63a5\uff0c\u5176\u4f59\u7684\u4f7f\u7528>\u7b26\u53f7\u8fdb\u884c\u8fde\u63a5\u3002<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>WITH subcom_cte (id, subcompanyname, companyid, supsubcomid, canceled, level, path) AS (\r\n    SELECT id, subcompanyname, companyid, supsubcomid, canceled, 1,\r\n        CAST(subcompanyname AS nvarchar(max))\r\n    FROM hrmsubcompany\r\n    WHERE supsubcomid = 0 AND canceled = 0\r\n    UNION ALL\r\n    SELECT s.id, s.subcompanyname, s.companyid,\r\n        s.supsubcomid,\r\n        s.canceled,\r\n        c.level + 1,\r\n        CAST(c.path + ' > ' + s.subcompanyname AS nvarchar(max))\r\n    FROM hrmsubcompany s\r\n    JOIN subcom_cte c ON s.supsubcomid = c.id\r\n    WHERE s.canceled = 0\r\n),\r\ndep_cte (id,\r\n         departmentname,\r\n         subcompanyid1,\r\n         supdepid,\r\n         canceled,\r\n         level,\r\n         path) AS (\r\n             SELECT id,\r\n                    departmentname,\r\n                    subcompanyid1,\r\n                    supdepid,\r\n                    canceled,\r\n                    1,\r\n                    CAST(departmentname AS nvarchar(max))\r\n             FROM hrmdepartment\r\n             WHERE supdepid = 0 AND canceled = 0\r\n             UNION ALL\r\n             SELECT d.id,\r\n                    d.departmentname,\r\n                    d.subcompanyid1,\r\n                    d.supdepid,\r\n                    d.canceled,\r\n                    c.level + 1,\r\n                    CAST(c.path + ' > ' + d.departmentname AS nvarchar(max))\r\n             FROM hrmdepartment d\r\n             JOIN dep_cte c ON d.supdepid = c.id\r\n             WHERE d.canceled = 0)\r\nSELECT (co.companyname + ' || ' + sc.path + ' || ' + d.path) AS \u7ec4\u7ec7\u67b6\u6784\r\nFROM HrmCompany co\r\nJOIN subcom_cte sc ON co.id = sc.companyid\r\nJOIN dep_cte d ON sc.id = d.subcompanyid1 ORDER BY \u7ec4\u7ec7\u67b6\u6784;\r\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u5f88\u591a\u65f6\u5019\u8981\u7528\u5230\u7ec4\u7ec7\u67b6\u6784\uff0c\u6bd4\u5982\u77e9\u9635\u7f16\u8f91\u3001\u884c\u653f\u53d1\u6587\u7b49\uff0c\u6211\u4eec\u53ef\u4ee5\u76f4\u63a5\u4ece\u6570\u636e\u5e93\u4e2d\u67e5\u8be2\u83b7\u5f97\uff0c\u6cdb\u5fae\u5177\u6709\u516c\u53f8\u3001\u5206\u90e8\u3001\u90e8\u95e83\u4e2a\u7ef4\u5ea6\uff0c\u5728\u8be5SQL\u4e2d\uff0c\u4e0d\u540c\u7ef4\u5ea6\u4f7f\u7528||\u7b26\u53f7\u8fdb\u884c\u8fde\u63a5\uff0c\u5176\u4f59\u7684\u4f7f\u7528>\u7b26\u53f7\u8fdb\u884c\u8fde\u63a5\u3002<\/p>\n","protected":false},"author":1,"featured_media":592,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[46,35],"tags":[61,50],"topic":[],"class_list":["post-808","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oa","category-course","tag-sql","tag-50"],"_links":{"self":[{"href":"https:\/\/www.furyblog.com\/index.php?rest_route=\/wp\/v2\/posts\/808","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.furyblog.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.furyblog.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.furyblog.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.furyblog.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=808"}],"version-history":[{"count":1,"href":"https:\/\/www.furyblog.com\/index.php?rest_route=\/wp\/v2\/posts\/808\/revisions"}],"predecessor-version":[{"id":809,"href":"https:\/\/www.furyblog.com\/index.php?rest_route=\/wp\/v2\/posts\/808\/revisions\/809"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.furyblog.com\/index.php?rest_route=\/wp\/v2\/media\/592"}],"wp:attachment":[{"href":"https:\/\/www.furyblog.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=808"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.furyblog.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=808"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.furyblog.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=808"},{"taxonomy":"topic","embeddable":true,"href":"https:\/\/www.furyblog.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftopic&post=808"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}