利用一隻單純的SQL指令來呈現出公司組織圖的關係

by Daphne Lin 5. 十二月 2010 23:04

作者:羅駿紘

精誠資訊 恆逸教育訓練中心 資深講師



我們都知道,在公司中,每個員工都應該有主管(除了總經理),而且主管也算是公司的員工之一,也是身為某人的下屬。但關聯式資料庫存放資料的方式卻不是以階層式的方式來存放,因此想要只利用一隻單純的SQL指令來呈現出公司組織圖的關係(如下圖),基本上是不容易的,通常需要再搭配其他的程式語言才能做到。

clip_image002

然而,Oracle早就想到這部份,因此陸續提供幾個功能來解決這個問題。


方法一:透過階層式查詢(Hierarchical Query)

SELECT employee_id, LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_') AS org_chart,manager_id , level

FROM employees

START WITH first_name='Steven' AND last_name='King'

CONNECT BY PRIOR employee_id=manager_id

order by level;


方法二:透過RECURSIVE WITH

WITH

reports_to_100 (eid, emp_last, mgr_id, reportLevel) AS

( --Anchor Member(不變成員)

SELECT employee_id, last_name, manager_id, 1 reportLevel

FROM employees

WHERE employee_id = 100

UNION ALL

--Recursive Member(遞迴成員)

SELECT e.employee_id, e.last_name, e.manager_id, reportLevel+1

FROM reports_to_100 r, employees e

WHERE r.eid = e.manager_id

)

SELECT eid, LPAD(emp_last, LENGTH(emp_last)+(reportLevel*2)-2,'_')

AS org_chart, mgr_id, reportLevel

FROM reports_to_100;


以上二種方法都可以得到相同結果如下。

clip_image006



您可在課程中了解更多的技術

相關學習資源︰【SQL】Oracle資料庫之SQL入門

Tags:

Oracle資料庫 | 羅駿紘Benson Lo

評論 (47) -

cours de theatre
cours de theatre United States
2017/9/30 上午 08:32:52 #

I really like and appreciate your article post.Thanks Again. Want more.

回覆

can ho dic phoenix
can ho dic phoenix United States
2017/10/7 上午 02:11:49 #

I really enjoy the article.Thanks Again. Keep writing.

回覆

Osimi sea view
Osimi sea view United States
2017/10/9 下午 07:59:56 #

Enjoyed every bit of your blog post.Much thanks again. Awesome.

回覆

buy hacklinks
buy hacklinks United States
2017/10/12 下午 10:38:06 #

Thanks for sharing, this is a fantastic blog post. Cool.

回覆

dragon city hacked apk
dragon city hacked apk United States
2017/10/15 下午 05:25:24 #

Thank you for your blog.Much thanks again. Want more.

回覆

browse around this site
browse around this site United States
2017/10/17 下午 04:44:18 #

wow, awesome article.Really looking forward to read more.

回覆

sletrokor review
sletrokor review United States
2017/10/17 下午 10:15:47 #

This is one awesome article post.Much thanks again. Cool.

回覆

VigRx Plus Review
VigRx Plus Review United States
2017/10/19 上午 09:19:41 #

Thank you for your article post. Awesome.

回覆

pop over to this site
pop over to this site United States
2017/10/19 下午 08:27:43 #

A big thank you for your article.Much thanks again.

回覆

prix carte grise
prix carte grise United States
2017/10/21 上午 09:27:24 #

Thanks a lot for the blog post.Really looking forward to read more. Keep writing.

回覆

phentaslim review
phentaslim review United States
2017/11/3 上午 07:51:38 #

I think this is a real great blog post.Really looking forward to read more. Fantastic.

回覆

back pain fitness inspiration
back pain fitness inspiration United States
2017/11/15 上午 09:58:40 #

Im thankful for the post.Much thanks again. Want more.

回覆

avocat criminel quebec
avocat criminel quebec United States
2017/11/16 下午 08:32:27 #

This is one awesome article.

回覆

Great blog post.Really thank you! Fantastic.

回覆

bikinis
bikinis United States
2017/11/24 上午 12:05:50 #

Enjoyed every bit of your blog.Really thank you! Want more.

回覆

Chad Boonswang and Jeffrey Goodman
Chad Boonswang and Jeffrey Goodman United States
2017/11/26 下午 08:09:50 #

Thank you ever so for you article.Really looking forward to read more. Will read on...

回覆

Chad Boonswang SEO
Chad Boonswang SEO United States
2017/11/27 上午 02:20:48 #

Thanks so much for the article.Much thanks again. Really Great.

回覆

truck wreckers scammer
truck wreckers scammer United States
2017/11/29 下午 06:43:35 #

I appreciate you sharing this blog post.Much thanks again. Fantastic.

回覆

can ho 4 mat tien
can ho 4 mat tien United States
2017/11/30 上午 01:22:16 #

Say, you got a nice blog.Thanks Again. Awesome.

回覆

small business loans
small business loans United States
2017/12/1 上午 03:06:49 #

wow, awesome article.Much thanks again. Much obliged.

回覆

porno
porno United States
2017/12/1 下午 06:49:49 #

Thanks so much for the article.Really looking forward to read more. Awesome.

回覆

Initial coin offering
Initial coin offering United States
2017/12/3 上午 07:01:50 #

Appreciate you sharing, great blog. Will read on...

回覆

hentai mobile
hentai mobile United States
2017/12/5 上午 11:52:06 #

I value the post.Really looking forward to read more. Cool.

回覆

Really enjoyed this post.Much thanks again. Much obliged.

回覆

lawyers site
lawyers site United States
2017/12/10 下午 09:25:29 #

I really liked your article.Much thanks again. Keep writing.

回覆

Donnie Shost
Donnie Shost United States
2017/12/14 上午 11:31:41 #

Hey, thanks for the blog post. Great.

回覆

look what i found
look what i found United States
2017/12/14 下午 06:25:43 #

Thanks again for the blog.Thanks Again. Much obliged.

回覆

Christmas
Christmas United States
2017/12/15 上午 01:01:11 #

Muchos Gracias for your post.Thanks Again. Great.

回覆

canon driver software
canon driver software United States
2017/12/16 下午 08:05:28 #

Great blog article.Thanks Again.

回覆

tips weight loss
tips weight loss United States
2017/12/17 上午 02:28:43 #

Appreciate you sharing, great article post. Want more.

回覆

Thanks a lot for the blog.Really thank you! Will read on...

回覆

Transition
Transition United States
2017/12/17 下午 07:41:21 #

Muchos Gracias for your blog post. Keep writing.

回覆

Darwin Horan
Darwin Horan United States
2017/12/23 下午 08:45:59 #

Very informative article post. Awesome.

回覆

Fantastic article.Much thanks again. Really Great.

回覆

hp driver
hp driver United States
2017/12/25 下午 10:13:21 #

Thanks a lot for the blog.Thanks Again. Cool.

回覆

آینه بغل
آینه بغل United States
2017/12/26 上午 10:03:23 #

Very neat blog article.Much thanks again. Great.

回覆

canon drivers
canon drivers United States
2017/12/27 下午 03:08:34 #

Really enjoyed this blog article.Much thanks again. Fantastic.

回覆

drivers hp
drivers hp United States
2018/1/2 上午 05:53:35 #

Really appreciate you sharing this blog article.Thanks Again.

回覆

click site
click site United States
2018/1/2 下午 01:51:06 #

I really enjoy the article.Really looking forward to read more.

回覆

Really informative blog.Really thank you! Much obliged.

回覆

printer driver
printer driver United States
2018/1/3 上午 05:10:00 #

Great, thanks for sharing this article.Much thanks again. Really Cool.

回覆

online casinos for real money usa
online casinos for real money usa United States
2018/1/4 下午 03:41:41 #

This is one awesome blog post.Thanks Again. Cool.

回覆

hp driver
hp driver United States
2018/1/5 下午 06:32:40 #

Thank you ever so for you article. Cool.

回覆

FBA
FBA United States
2018/1/6 上午 10:38:51 #

Really appreciate you sharing this post.Thanks Again. Want more.

回覆

colocation miami
colocation miami United States
2018/1/10 上午 10:34:35 #

Wow, great article.Thanks Again. Will read on...

回覆

Srge
Srge United States
2019/11/7 下午 10:53:35 #

723849 542748you could have a great weblog here! would you wish to make some invite posts on my weblog? 512796

回覆

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List