{"id":2039,"date":"2025-07-24T20:52:07","date_gmt":"2025-07-24T12:52:07","guid":{"rendered":"https:\/\/note.ui11.cc\/?p=2039"},"modified":"2025-07-25T08:38:54","modified_gmt":"2025-07-25T00:38:54","slug":"%e7%94%a8%e5%8f%8b-%e6%8a%a5%e8%a1%a8%e5%ad%a6%e4%b9%a0%e6%80%bb%e7%bb%93","status":"publish","type":"post","link":"https:\/\/note.ui11.cc\/index.php\/2025\/07\/24\/%e7%94%a8%e5%8f%8b-%e6%8a%a5%e8%a1%a8%e5%ad%a6%e4%b9%a0%e6%80%bb%e7%bb%93\/","title":{"rendered":"\u7528\u53cb–\u62a5\u8868\u5b66\u4e60\u603b\u7ed3"},"content":{"rendered":"\n

\u62a5\u8868\u5176\u5b9e\u5c31\u662f\u6839\u636e\u5ba2\u6237\u9700\u6c42–\u9700\u8981\u54ea\u4e9b\u5b57\u6bb5\u53cd\u9988\u7ed9\u7532\u65b9\uff0c\u6bd4\u5982\u6211\u4eec\u6709\u4e00\u5343\u591a\u4e2a\u8868\uff0c\u9700\u8981\u7684\u5b57\u6bb5\u5728\u5404\u4e2a\u8868\u4e2d\u90fd\u53ef\u80fd\u4f1a\u6709\u6d89\u53ca<\/p>\n\n\n\n

\u9996\u5148\u5230\u8bed\u4e49\u5efa\u6a21\u8fdb\u884csql\u7684\u62fc\u5199<\/p>\n\n\n\n

\"\"<\/div><\/figure>\n\n\n\n

\u5728\u8fd9\u91cc\u53ef\u4ee5\u6839\u636e\u5ba2\u6237\u7ed9\u7684excel\u6bd4\u5982<\/p>\n\n\n\n

\"\"<\/div><\/figure>\n\n\n\n

\u8fd9\u79cd\uff0c\u5bf9\u6b64\u8fdb\u884csql\uff0c\u6211\u7684sql\u5982\u4e0b\uff1a<\/p>\n\n\n\n

WITH RECURSIVE org_hierarchy AS (\n  SELECT \n    id,\n    parentid,\n    name,\n    1 AS level,\n    CAST(name AS CHAR(255)) AS full_path\n  FROM iuap_apdoc_basedoc.org_admin\n  WHERE parentid NOT IN (SELECT id FROM iuap_apdoc_basedoc.org_admin)\n  UNION ALL\n  SELECT \n    o.id,\n    o.parentid,\n    o.name,\n    oh.level + 1,\n    CONCAT(oh.full_path, '\/', o.name)\n  FROM iuap_apdoc_basedoc.org_admin o\n  JOIN org_hierarchy oh ON o.parentid = oh.id\n)\nSELECT  \n  count(*) as '\u4eba\u6570',\n  SUBSTRING_INDEX(SUBSTRING_INDEX(oh.full_path, '\/', 2), '\/', -1) as \u4e0a\u7ea7\u90e8\u95e8,\n  SUBSTRING_INDEX(SUBSTRING_INDEX(oh.full_path, '\/', 3), '\/', -1) as \u4e8c\u7ea7\u90e8\u95e8,\n  CASE \n    WHEN LOCATE('\u7701', t3.name) > 0 THEN LEFT(t3.name, LOCATE('\u7701', t3.name))\n    ELSE t3.name\n  END as '\u7c4d\u8d2f'\nFROM iuap_apdoc_basedoc.bd_staff t1\nJOIN iuap_apdoc_basedoc.org_admin t2 ON t1.dept_id = t2.id\nJOIN iuap_apdoc_basedoc.bd_baseregion t3 ON t1.origin = t3.id\nJOIN org_hierarchy oh ON oh.id = t1.dept_id\nWHERE 1=1\n<#if param('\u90e8\u95e8')!=''>\n   AND t1.dept_id IN (\n    <#list param('\u90e8\u95e8') ? split(\",\") as item > '${item}' <#if item ? has_next >,<\/#if > <\/#list >\n  )\n<\/#if>\nGROUP BY \n  t2.name,\n  SUBSTRING_INDEX(SUBSTRING_INDEX(oh.full_path, '\/', 2), '\/', -1),\n  SUBSTRING_INDEX(SUBSTRING_INDEX(oh.full_path, '\/', 3), '\/', -1),\n  CASE \n    WHEN LOCATE('\u7701', t3.name) > 0 THEN LEFT(t3.name, LOCATE('\u7701', t3.name))\n    ELSE t3.name\n  END<\/code><\/pre>\n\n\n\n
\"\"<\/div><\/figure>\n\n\n\n
\u8bed\u4e49\u5efa\u6a21\u8fbe\u5230\u76ee\u7684\u540e\u5c31\uff08\u53d1\u5e03\u540e\u671f\u518d\u8bbe\u8ba1\u62a5\u8868\u7684\u65f6\u5019\u53ef\u4ee5\u5f15\u7528\uff09<\/p>\n\n\n\n
\u62a5\u8868\u8bbe\u8ba1\uff1a<\/strong><\/p>\n\n\n\n
\"\"<\/div><\/figure>\n\n\n\n
\u6839\u636e\u6211\u4eec\u7684\u8981\u6c42\u8fdb\u884c\u53c2\u6570\u4e0e\u5b57\u6bb5\u7684\u62fc\u63a5\uff1a<\/p>\n\n\n\n
\"\"<\/div><\/figure>\n\n\n\n
\u70b9\u5230\u67d0\u4e2a\u5b57\u6bb5\u540e\u53ef\u4ee5\u8fdb\u884c\u8bbe\u7f6e\uff1a<\/p>\n\n\n\n
\"\"<\/div><\/figure>\n\n\n\n
\u7136\u540e<\/p>\n\n\n\n
\"\"<\/div><\/figure>\n\n\n\n
\u8fd9\u662f\u6548\u679c\uff01<\/p>\n","protected":false},"excerpt":{"rendered":"
\u62a5\u8868\u5176\u5b9e\u5c31\u662f\u6839\u636e\u5ba2\u6237\u9700\u6c42–\u9700\u8981\u54ea\u4e9b\u5b57\u6bb5\u53cd\u9988\u7ed9\u7532\u65b9\uff0c\u6bd4\u5982\u6211\u4eec\u6709\u4e00\u5343\u591a\u4e2a\u8868\uff0c\u9700\u8981\u7684\u5b57\u6bb5\u5728\u5404\u4e2a\u8868\u4e2d\u90fd\u53ef\u80fd […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29],"tags":[],"class_list":["post-2039","post","type-post","status-publish","format-standard","hentry","category-yonyou"],"_links":{"self":[{"href":"https:\/\/note.ui11.cc\/index.php\/wp-json\/wp\/v2\/posts\/2039","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/note.ui11.cc\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/note.ui11.cc\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/note.ui11.cc\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/note.ui11.cc\/index.php\/wp-json\/wp\/v2\/comments?post=2039"}],"version-history":[{"count":2,"href":"https:\/\/note.ui11.cc\/index.php\/wp-json\/wp\/v2\/posts\/2039\/revisions"}],"predecessor-version":[{"id":2049,"href":"https:\/\/note.ui11.cc\/index.php\/wp-json\/wp\/v2\/posts\/2039\/revisions\/2049"}],"wp:attachment":[{"href":"https:\/\/note.ui11.cc\/index.php\/wp-json\/wp\/v2\/media?parent=2039"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/note.ui11.cc\/index.php\/wp-json\/wp\/v2\/categories?post=2039"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/note.ui11.cc\/index.php\/wp-json\/wp\/v2\/tags?post=2039"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}