iT邦幫忙

3

以Postgresql為主,再聊聊資料庫 遞迴查詢之找出循環參考

資料庫的遞迴查詢,是很強大的功能,筆者以前使用 IBM DB2 時,
就很喜歡使用遞迴查詢,因為可以幫助解決許多問題.
在過往的問答中,也有一些使用遞迴查詢的例子.

接著我們來看遞迴查詢中會出現的循環參考.以經典的遞迴查詢
案例,組織層級來產生測試資料.為了簡化起見,這裡沒有做FK.


create table it200508 (
  id integer not null primary key
, mgr_id integer not null 
);

insert into it200508
select n
     , (random() * 100)::int
  from generate_series (1, 100) as g(n);
  
commit;

接下來我們利用 PostgreSQL 的 Array , 還有其函數 any().
就可以很方便的找出來是否有循環參考. 

with recursive cte (
  id
, mgr_id
, depth
, path
, is_cycle
) as (
select i.id
     , i.mgr_id
     , 1
     , array[i.id]
     , false
  from it200508 i
 union all
select i.id
     , i.mgr_id
     , c.depth + 1
     , c.path || i.id
     , i.id = any(path) 
  from it200508 i
     , cte c
 where i.id = c.mgr_id
   and not is_cycle
)
select depth
     , path
     , is_cycle
  from cte
 where is_cycle;

很巧的也有100組.鑑於本站設計的關係,
程式碼部分很窄,所以用貼圖.

https://ithelp.ithome.com.tw/upload/images/20200508/20050647B2TmqMBcQH.png

https://ithelp.ithome.com.tw/upload/images/20200508/20050647qLSEMhdgGV.png


1 則留言

0
ckp6250
iT邦研究生 2 級 ‧ 2020-05-10 05:35:38

【遞迴查詢】的確是一個很強大的功能,
早期mysql還沒有提供此功能時,要做這類『組織層級』或『親屬關係』之類的查詢,
都很費勁。

並請教殺豬大,mariadb 有沒有類似的底下的功能啊?
https://blog.xuite.net/tolarku/blog/21137675-SQL+%E5%BB%BA%E7%AB%8B%E9%81%A0%E7%AB%AF%E8%B3%87%E6%96%99%E5%BA%AB%E8%B3%87%E6%96%99%E8%A1%A8%E5%88%B0%E6%9C%AC%E5%9C%B0+Table+%E6%88%96+VIEW

看更多先前的回應...收起先前的回應...

https://ithelp.ithome.com.tw/articles/10185996

MySQL 原本有一個 MySQL Federated storage engine
以下2010年時我寫的範例
http://phorum.study-area.org/index.php?topic=63818.0

後續在 ithelp 裡,也有回答時使用這個引擎,上面那篇是網友寫的介紹
MariaDB 相關的功能.

ckp6250 iT邦研究生 2 級 ‧ 2020-05-10 12:10:23 檢舉

十分感恩,
我好好研讀一下,近來有一個案子有此需求。

ckp6250 iT邦研究生 2 級 ‧ 2020-05-10 15:16:22 檢舉

測試成功,十分感謝!
兩邊都要同時安裝 INSTALL SONAME 'ha_federatedx';

我一開始只安裝了主呼叫端,被呼叫端沒有安裝,
執行時,跑了半天,跑不出來,訊息只說連不上,也沒很清楚標示。

等兩邊都裝上後,就很順利了。

恭喜測試成功! 老前輩過段時間可以把這個的使用心得跟大家分享一下.

ckp6250 iT邦研究生 2 級 ‧ 2020-05-11 08:58:28 檢舉

  我的運用在於,客戶的出貨單sql資料在他公司主機內,是desktop程式,用vfp寫的,行之有年,客戶不想變動。

  現在要使用電子發票了,但我早己寫成web版,sql在我這兒,我不想重寫desktop的電子發票,所以,才想要利用兩邊的sql協同作業。

  我隠約感覺,這樣的跨網域協同模式,似乎有很多運用空間,只是,還不知道『效率』如何,要實做才知道。

sujunmin iT邦新手 5 級 ‧ 2020-05-19 12:53:56 檢舉

謝謝殺豬大推小弟的連結,有錯請不吝指正~

後來有一些上線紀錄,更新到我自己的 blog 上,有需要再參考看看~

不是殺豬大.....你想想 倚天屠龍記 改叫 倚天殺龍記,就有點落漆了.

ckp6250 iT邦研究生 2 級 ‧ 2020-05-19 16:06:20 檢舉

  我是覺得『殺』字較接地氣,你想想 吞藥自殺 改叫 吞藥自屠,就有點不知所云了。

  電影拍完叫做『殺青』,改叫『屠青』怎麼聽怎麼怪!

  張獻忠有一首詩:「天生萬物以養人,人無一德以報天,殺!殺!殺!殺!殺!殺!殺!」,若改成「屠!屠!屠!屠!屠!屠!屠!」唱起來氣勢弱很多。

  黃巢:「待到秋來九月八,我花開後百花殺。沖天香陣透長安,滿城盡帶黃金甲。」改成「我花開後百花屠」?難聽又沒力!

  以上,還是稱殺豬大比較氣派。

屠有殘殺的意思,且通常是指牲畜
雖然有但應該很少很少人會覺得自己是牲畜...所以吞藥自屠...
相近的還有戮、滅、弑等等
戮的話覺得有點殘忍但可能會被當變態(?
弑就不要比較好,通常是下對上
滅感覺最霸氣,不留活口,連灰塵都不剩!

我來解釋一下,叫一級屠豬士,是與屠龍之技及沒吃過豬肉,也看過豬走路這兩個大家耳熟能詳的有關.因為我在這裡大部分寫的或回答的,都不是多高深的東西,所以不到屠龍等級,而且是屬於一般常見,頂多是屠豬等級唯有熟練耳.既然熟練,那就自誇一級吧.

ckp6250 iT邦研究生 2 級 ‧ 2020-06-05 11:48:08 檢舉

聽起來合情合理,可以接受,
不過,若要尊稱【一級屠豬士大大】,字數太多不好打。
若稱【一大】又怪怪地。

可以複製(Ctrl-c)貼上(Ctrl-p)

我要留言

立即登入留言