{"id":121,"date":"2024-10-30T17:48:46","date_gmt":"2024-10-30T09:48:46","guid":{"rendered":"https:\/\/zhoujibin.com\/?p=121"},"modified":"2025-03-10T11:10:27","modified_gmt":"2025-03-10T03:10:27","slug":"sql%e9%80%92%e5%bd%92%e6%9f%a5%e6%89%be-oracle","status":"publish","type":"post","link":"https:\/\/zhoujibin.com\/?p=121","title":{"rendered":"SQL\u9012\u5f52\u67e5\u627e-ORACLE"},"content":{"rendered":"\n

\u793a\u4f8b\u8868<\/h1>\n\n\n\n

\u5b57\u6bb5<\/h2>\n\n\n\n
\u5b57\u6bb5\u540d<\/th>\u5b57\u6bb5\u6ce8\u91ca<\/th><\/tr><\/thead>
org_code<\/td>\u673a\u6784\u4ee3\u7801<\/td><\/tr>
org_name<\/td>\u673a\u6784\u540d\u79f0<\/td><\/tr>
parent_org_code<\/td>\u4e0a\u7ea7\u673a\u6784\u4ee3\u7801<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

\u5efa\u8868SQL<\/h2>\n\n\n\n
-- Create table\ncreate table t_demo_org\n(\n  org_code        varchar(32),\n  org_name        varchar(100),\n  parent_org_code varchar(32)\n)\n;\n-- Add comments to the columns \ncomment on column t_demo_org.org_code\n  is '\u673a\u6784\u4ee3\u7801';\ncomment on column t_demo_org.org_name\n  is '\u673a\u6784\u540d\u79f0';\ncomment on column t_demo_org.parent_org_code\n  is '\u4e0a\u7ea7\u673a\u6784\u4ee3\u7801';<\/code><\/pre>\n\n\n\n
\u793a\u4f8b\u6570\u636e<\/h1>\n\n\n\n
\u6570\u636e\u9884\u89c8<\/h2>\n\n\n\n
\u673a\u6784\u4ee3\u7801<\/th>\u673a\u6784\u540d\u79f0<\/th>\u4e0a\u7ea7\u673a\u6784\u4ee3\u7801<\/th><\/tr><\/thead>
org_code1<\/td>org_name1<\/td>org_code0<\/td><\/tr>
org_code2<\/td>org_name2<\/td>org_code1<\/td><\/tr>
org_code3<\/td>org_name3<\/td>org_code2<\/td><\/tr>
org_code4<\/td>org_name4<\/td>org_code3<\/td><\/tr>
org_code5<\/td>org_name5<\/td>org_code4<\/td><\/tr>
org_code6<\/td>org_name6<\/td>org_code5<\/td><\/tr>
org_code7<\/td>org_name7<\/td>org_code6<\/td><\/tr>
org_code8<\/td>org_name8<\/td>org_code7<\/td><\/tr>
org_code9<\/td>org_name9<\/td>org_code8<\/td><\/tr>
org_code10<\/td>org_name10<\/td>org_code9<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\u65b0\u589eSQL<\/h2>\n\n\n\n
insert into t_demo_org values ('org_code1', 'org_name1', 'org_code0');\ninsert into t_demo_org values ('org_code2', 'org_name2', 'org_code1');\ninsert into t_demo_org values ('org_code3', 'org_name3', 'org_code2');\ninsert into t_demo_org values ('org_code4', 'org_name4', 'org_code3');\ninsert into t_demo_org values ('org_code5', 'org_name5', 'org_code4');\ninsert into t_demo_org values ('org_code6', 'org_name6', 'org_code5');\ninsert into t_demo_org values ('org_code7', 'org_name7', 'org_code6');\ninsert into t_demo_org values ('org_code8', 'org_name8', 'org_code7');\ninsert into t_demo_org values ('org_code9', 'org_name9', 'org_code8');\ninsert into t_demo_org values ('org_code10', 'org_name10', 'org_code9');<\/code><\/pre>\n\n\n\n
\u9012\u5f52\u67e5\u627e<\/h1>\n\n\n\n
\u4e3a\u4e86\u65b9\u4fbf\u67e5\u770b\u6570\u636e\uff0c\u4ee5\u7b2c5\u6761\u6570\u636e\u4e3a\u5206\u754c\u70b9\u3002<\/p>\n\n\n\n
SQL<\/h2>\n\n\n\n
oracle<\/h3>\n\n\n\n
select *\n  from t_demo_org o\n  -- \u6307\u5b9a\u8d77\u59cb\u67e5\u8be2\u6761\u4ef6\n start with o.org_code = 'org_code5'\n -- \u5173\u8054\u67e5\u8be2\u6761\u4ef6\uff0c\u4ee5\u5f53\u524d\u6570\u636e\u4e3a\u57fa\u51c6\uff0c\u3010\u5411\u4e0a\u3011\u9012\u5f52\u67e5\u8be2\nconnect by o.org_code = prior o.parent_org_code;\n-- \u5173\u8054\u67e5\u8be2\u6761\u4ef6\uff0c\u4ee5\u5f53\u524d\u6570\u636e\u4e3a\u57fa\u51c6\uff0c\u3010\u5411\u4e0b\u3011\u9012\u5f52\u67e5\u8be2\n-- connect by o.parent_org_code = prior o.org_code;<\/code><\/pre>\n\n\n\n
postgresql<\/h3>\n\n\n\n
 with RECURSIVE tmp as (\n  -- \u6307\u5b9a\u8d77\u59cb\u67e5\u8be2\u6761\u4ef6\n  select * from t_demo_org o where o.org_code = 'org_code1'\n  union \n -- \u5173\u8054\u67e5\u8be2\u6761\u4ef6\uff0c\u4ee5\u5f53\u524d\u6570\u636e\u4e3a\u57fa\u51c6\uff0c\u3010\u5411\u4e0a\u3011\u9012\u5f52\u67e5\u8be2\n  select po.* from t_demo_org po join tmp on  po.org_code = tmp.parent_org_code\n -- \u5173\u8054\u67e5\u8be2\u6761\u4ef6\uff0c\u4ee5\u5f53\u524d\u6570\u636e\u4e3a\u57fa\u51c6\uff0c\u3010\u5411\u4e0b\u3011\u9012\u5f52\u67e5\u8be2\n -- select po.* from t_demo_org po join tmp on  po.parent_org_code = tmp.org_code\n ) select * from tmp<\/code><\/pre>\n\n\n\n
\u7ed3\u679c<\/h2>\n\n\n\n
\u5411\u4e0a\u9012\u5f52\u67e5\u8be2<\/h3>\n\n\n\n
\u673a\u6784\u4ee3\u7801<\/th>\u673a\u6784\u540d\u79f0<\/th>\u4e0a\u7ea7\u673a\u6784\u4ee3\u7801<\/th><\/tr><\/thead>
org_code5<\/td>org_name5<\/td>org_code4<\/td><\/tr>
org_code4<\/td>org_name4<\/td>org_code3<\/td><\/tr>
org_code3<\/td>org_name3<\/td>org_code2<\/td><\/tr>
org_code2<\/td>org_name2<\/td>org_code1<\/td><\/tr>
org_code1<\/td>org_name1<\/td>org_code0<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\u5411\u4e0b\u9012\u5f52\u67e5\u8be2<\/h3>\n\n\n\n
\u673a\u6784\u4ee3\u7801<\/th>\u673a\u6784\u540d\u79f0<\/th>\u4e0a\u7ea7\u673a\u6784\u4ee3\u7801<\/th><\/tr><\/thead>
org_code5<\/td>org_name5<\/td>org_code4<\/td><\/tr>
org_code6<\/td>org_name6<\/td>org_code5<\/td><\/tr>
org_code7<\/td>org_name7<\/td>org_code6<\/td><\/tr>
org_code8<\/td>org_name8<\/td>org_code7<\/td><\/tr>
org_code9<\/td>org_name9<\/td>org_code8<\/td><\/tr>
org_code10<\/td>org_name10<\/td>org_code9<\/td><\/tr><\/tbody><\/table><\/figure>\n","protected":false},"excerpt":{"rendered":"
\u793a\u4f8b\u8868 \u5b57\u6bb5 \u5b57\u6bb5\u540d \u5b57\u6bb5\u6ce8\u91ca org_code \u673a\u6784\u4ee3\u7801 org_name \u673a\u6784\u540d\u79f0 parent_org_ […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14],"tags":[27,26],"class_list":["post-121","post","type-post","status-publish","format-standard","hentry","category-database","tag-oracle","tag-sql"],"_links":{"self":[{"href":"https:\/\/zhoujibin.com\/index.php?rest_route=\/wp\/v2\/posts\/121"}],"collection":[{"href":"https:\/\/zhoujibin.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhoujibin.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhoujibin.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhoujibin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=121"}],"version-history":[{"count":2,"href":"https:\/\/zhoujibin.com\/index.php?rest_route=\/wp\/v2\/posts\/121\/revisions"}],"predecessor-version":[{"id":230,"href":"https:\/\/zhoujibin.com\/index.php?rest_route=\/wp\/v2\/posts\/121\/revisions\/230"}],"wp:attachment":[{"href":"https:\/\/zhoujibin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=121"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhoujibin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=121"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhoujibin.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=121"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}