轉載請注明出處: https://www.cnblogs.com/funnyzpc/p/13698249.html
也是偶然的一次，群友出了一道題考考大家,當時正值疫情最最嚴重的三月(借口...)，披著外套，天氣也不是很好(借口...)，耐著性子花了5分鐘理解了下題，
第一個5分鐘...無解，再第二個5分鐘，，，無解，還第三個5分鐘，，，終究無解(之所以如此可能是題目太吸引我了吧)，之后又忙于各種瑣事，一直到離職后重新找作業，
一再想想這事兒還是不能再拖了，終于 就到了今天...接下來開始表演了??????
chapter One:題目
-
將下表源資料排列成指定順序(看完題目請先思考幾分鐘)
- 源資料
id p_id name 1 0 防控點級別 2 0 道路標準 3 0 應急回應等級 10000 1 一級 10001 1 二級 10002 1 三級 10003 1 四級 10004 1 五級 10005 2 主干路 10006 2 次干路 10007 2 支路 10008 2 城市下立交區 - 排列結果
id p_id name 1 防控點級別 0 10000 一級 1 10001 二級 1 10002 三級 1 10003 四級 1 10004 五級 1 2 道路標準 0 10005 主干路 2 10011 邊支路1 10005 10012 邊支路2 10005 10006 次干路 2 10007 支路 2 10008 城市下立交區 2 3 應急回應等級 0
chapter Two:亮出SQL
思考完畢，想必這會兒可以小試牛刀了，這就提供下SQL
- 以下為create 及 必要的insert陳述句
DROP TABLE IF EXISTS "dicts";
CREATE TABLE "public"."dicts" (
"id" int8 NOT NULL,
"p_id" int8,
"name" varchar(50) COLLATE "pg_catalog"."default"
);
INSERT INTO "dicts" VALUES (10000, 10013, '一級');
INSERT INTO "dicts" VALUES (10001, 10013, '二級');
INSERT INTO "dicts" VALUES (10002, 10013, '三級');
INSERT INTO "dicts" VALUES (10003, 10013, '四級');
INSERT INTO "dicts" VALUES (10004, 10013, '五級');
INSERT INTO "dicts" VALUES (10005, 10011, '主干路');
INSERT INTO "dicts" VALUES (10006, 10011, '次干路');
INSERT INTO "dicts" VALUES (10007, 10011, '支路');
INSERT INTO "dicts" VALUES (10008, 10011, '城市下立交區');
INSERT INTO "dicts" VALUES (10011, 99999, '防控點級別');
INSERT INTO "dicts" VALUES (10012, 99999, '應急回應等級');
INSERT INTO "dicts" VALUES (10013, 99999, '道路標準');
chapter Three:再思考
可能很多IT朋友都很急不可耐想知道答案哈哈??，答案或許重要或許也不重要，首先您得有一個思考的程序，一開始我的思考程序是這樣的：
- 思考一：這可能就是個普通排序，我直接建一個數字列標記下不久得了(to simple...，如果真這樣還用考嘛)
- 思考二：拿出首列作為一個表和剩余列(也作為一個表)做聯合查詢，，，不行不行，SQL太復雜，后面也沒法排序這是個問題
- 思考三：使用遞回函式，但是遞回通常只取出指定記錄下級及分支級記錄，如果整體取出SQL太復雜(涉及到回圈排序)，，，這是個思路，但不完美
思考結果：
我仔細的分析了題目，得出如下結論: 這是一顆帶有遞回結構(思路)的遞回樹，之所以特意注明遞回結構是因為遞回出來的資料必須有一個帶有樹結構的欄位，
不然之后無法使用排序生成最終結果
雖說遞回解決了問題的第一步，后面我又碰到了問題的下一個重點：如何實作樹結構欄位列，終于我從實踐中找到了三個解決方案：
-
方案一: 將遞回后的結果按虛擬列(遞回順序列)及
p_id列排序，這樣貌似很簡單，SQL走起- 以下為SQL陳述句
-- SQL陳述句 with recursive tmp as ( select id, name, p_id,id::text as pcode from dicts where p_id=0 union all select origin.id, origin.name, tmp.id as p_id, tmp.id::text as pcode from tmp join dicts as origin on origin.p_id = tmp.id ) select * from tmp order by pcode asc,p_id asc;-
以下為執行結果
id p_id name pcode 1 防控點級別 0 1 10004 五級 1 1 10000 一級 1 1 10001 二級 1 1 10002 三級 1 1 10003 四級 1 1 2 道路標準 0 2 10005 主干路 2 2 10006 次干路 2 2 10007 支路 2 2 10008 城市下立交區 2 2 3 應急回應等級 0 3
-
結果也對，但是 如果
id列是varchr(字串)型別，這個排列順序結果就有問題，例如這樣id p_id name pcode 1 防控點級別 0 1 10004 五級 1 1 10000 一級 1 1 10001 二級 1 1 10002 三級 1 1 10003 四級 1 1 10012 邊支路2 10005 10005 10011 邊支路1 10005 10005 2 道路標準 0 2 10005 主干路 2 2 10006 次干路 2 2 10007 支路 2 2 10008 城市下立交區 2 2 3 應急回應等級 0 3 (注：我將id及p_id改為varchar型別并插入兩行記錄
10012及10011)
chapter Four:最終解決
可以看到，以上默認遞回排序在id及p_id為數字時是符合題目答案的，不過即使這倆欄位是數字在這兩種情況下也是有問題的:
- 這棵樹有三級及更多級時
- 手動ID大小反序時
- 遞回相關欄位為字符時(上文已提到)
對于這頭兩個種情況這里不做深入，各位自行測驗哈哈哈??
下面我就放出個人覺得合適的方案
-
方案二
-
使用遞回+array函式將每次回圈時產生的
depth(虛擬欄位)及id欄位放進path(虛擬欄位)并按其排序- SQL實作陳述句
WITH RECURSIVE tt (ID, NAME, p_id, PATH, DEPTH) AS ( SELECT ID, NAME, p_id, ARRAY[ID] AS PATH, 1 AS DEPTH FROM dicts WHERE p_id='0' UNION ALL SELECT D.ID, D.NAME, D.p_id, tt.PATH||D.ID, tt.DEPTH + 1 AS DEPTH FROM dicts D JOIN tt ON D.p_id = tt.ID ) SELECT ID, NAME, p_id,DEPTH,PATH FROM tt ORDER BY PATH;- SQL輸出結果
id name p_id depth path 1 防控點級別 0 1 {1} 10000 一級 1 2 {1,10000} 10001 二級 1 2 {1,10001} 10002 三級 1 2 {1,10002} 10003 四級 1 2 {1,10003} 10004 五級 1 2 {1,10004} 2 道路標準 0 1 {2} 10005 主干路 2 2 {2,10005} 10011 邊支路1 10005 3 {2,10005,10011} 10012 邊支路2 10005 3 {2,10005,10012} 10006 次干路 2 2 {2,10006} 10007 支路 2 2 {2,10007} 10008 城市下立交區 2 2 {2,10008} 3 應急回應等級 0 1 {3}
-
-
方案二
-
使用__遞回__+__視窗函式__將每次回圈時產生的
depth(虛擬欄位)及視窗函式產生的序列放進path(虛擬欄位)并按其排序 -
SQL實作陳述句
WITH RECURSIVE T (id, name, p_id,path,DEPTH) AS ( SELECT ID, NAME, p_id, row_number() over(order by id asc)::text AS PATH, 1 AS DEPTH FROM dicts WHERE p_id=0 UNION ALL SELECT D.ID, D.NAME, D.p_id, T.PATH||'-'||row_number() over(PARTITION by t.path order by d.id asc), T.DEPTH + 1 AS DEPTH FROM dicts D JOIN T ON D.p_id = T.ID ) SELECT ID, NAME,p_id,DEPTH,PATH FROM T ORDER BY PATH asc; -
SQL輸入結果
id name p_id depth path 1 防控點級別 0 1 1 10000 一級 1 2 1-1 10001 二級 1 2 1-2 10002 三級 1 2 1-3 10003 四級 1 2 1-4 10004 五級 1 2 1-5 2 道路標準 0 1 2 10005 主干路 2 2 2-1 10011 邊支路1 10005 3 2-1-1 10012 邊支路2 10005 3 2-1-2 10006 次干路 2 2 2-2 10007 支路 2 2 2-3 10008 城市下立交區 2 2 2-4 3 應急回應等級 0 1 3 可以看到方案三的輸出結構性更好(純個人摸索出來的);另~,ARRAY的方案 得感謝網友的博文??
finally：總結
首先，得說這是一道很好的SQL題，可不是嘛??????，讓我忙活了好一會兒呢，，，,值得一提的是這道題可以考遞回、排序、ARRAY(高級特性)、型別及型別轉換、當然還有視窗函式,
如果真有某面試官考這個，可就真坑...(￣y▽,￣)╭
另外,若內容有些許謬誤懇請指正哈，各位周末愉快，同時預祝各位一線碼農中秋快樂??
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/135540.html
標籤：PostgreSQL
上一篇：mysql like'%xx%' 查詢,使用索引的bug
下一篇：neo4j基礎-常用cql