SQL 問題...急...

angusmike

A  係B既上司
|
B 係C既上司 (LEVEL 1)
|
C (LEVEL 2)

empNo sempNo(佢上司)
A           
B            A
C            B

點先可以COUNT到佢 係LEVEL 1  定 LEVEL 2?

chiefumpire

一定要用 SQL ?
最多只有 LEVEL 2 ?

Kenny仔

count到架咩 你level2同level1都有上司
你要add番個column(level)

thinkpanda

原帖由 angusmike 於 2008-11-13 20:54 發表
A  係B既上司
|
B 係C既上司 (LEVEL 1)
|
C (LEVEL 2)

empNo sempNo(佢上司)
A           
B            A
C            B

點先可以COUNT到佢 係LEVEL 1  定 LEVEL 2?

it is a well known problem in relational database, known as "Parts Explosion Problem", which cannot simply be solved by single SQL statement.

You may want to modify the data structure to make it more SQL SELECT friendly (as a price of INSERT/UPDATE performance)

必思

原帖由 kamchihau 於 2008-11-13 22:36 發表
A : fieldserial=01
B : fieldserial=0101
C : fieldserial=010101
select * from table where fieldserial like '01%' and length(fieldserial)='2' ------------->level 1
select * from table where fieldserial  ...

咁設計，第時多個B1 under 阿B, B1做C既阿頭，你個query咪死

thinkpanda

我以前做過類似問題既做法係: (Staff, Ancestor) 結構, 用番樓主既例子

(A, A)
(B, A)
(B, B)
(C, A)
(C, B)
(C, C)

咁要數C係第幾層, 就只要 select count(*) from XXX where staff='C'
呢個方法亦避免左#6 既問題
要加個B1係B同C之間, 只需要insert record, 唔使update
Algorithm 係 :
1. 為新staff 既每個上司同自己加一個record. 即加入
(B1, A)
(B1, B)
(B1, B1)
可以用二句SQL 做到
insert into XXX (staff, ancestor) select 'B1', ancestor from XXX where staff='C'  /* C is the one being replaced */
insert into XXX (staff, ancestor) values ('B1', 'B1')
2. 為每個下屬加一個record, 即加入.
(C, B1)
可以用一句SQL 做到
insert into XXX (staff, ancestor) select staff, 'B1' from XXX where ancestor='C'
要移除一個staff, 只需要以下既SQL
delete from XXX where staff='B1' or ancestor='B1'
唔鍾意"or" 既 分兩句亦得.

要數下有幾多下屬都可以用一句SQL 搞掂
select count(*)-1 from staff where ancestor='B'

呢個結構既好處係可以用固定數目既SQL statement 做任何加減動作, 亦充份利用database index, 唔會涉及table scan 動作, 快速讀取資料....

[ 本帖最後由 thinkpanda 於 2008-11-14 11:11 編輯 ]

laputafish

原帖由 thinkpanda 於 2008-11-14 10:53 發表
我以前做過類似問題既做法係: (Staff, Ancestor) 結構, 用番樓主既例子

(A, A)
(B, A)
(B, B)
(C, A)
(C, B)
(C, C)

咁要數C係第幾層, 就只要 select count(*) from XXX where staff='C'
呢個方法亦避免左#6 既問 ...

但會否失去左d 資料, 好似 上司同佢下屬果個直屬關係.
例如  C 係直屬 B, 唔係直屬 A. 雖然 A 始終係o向 C 之上.

Rudy

原帖由 thinkpanda 於 2008-11-13 21:18 發表


it is a well known problem in relational database, known as "Parts Explosion Problem", which cannot simply be solved by single SQL statement.

You may want to modify the data structure to make it mo ...

我覺得你應該係教書的...... 利害.

thinkpanda

原帖由 laputafish 於 2008-11-14 13:42 發表

但會否失去左d 資料, 好似 上司同佢下屬果個直屬關係.
例如  C 係直屬 B, 唔係直屬 A. 雖然 A 始終係o向 C 之上.

咁得唔得, 好似有D煩,

select ancestor from XXX as x1
where staff='C'
and (select count(*) from XXX as x2 where x2.staff=x1.ancestor) = (select count(*) from XXX as x3 where x3.staff=x1.staff) -1