我有一個資料庫有兩個欄位
其中的記錄(以下舉例)

level1 level2
A
B C
C A
D A
E B

level1 為不重覆的代號, level2 是 level1 的上一階
我要調出 level1 的 E 最上階是誰(leve1)
結果-->
E 上一階 level2 是 B,
然後在 level1 找到有 B 而 level1 的 B 上一階是 level2 的 C
然後又在 leve1 找到有 C, 而 level1 的 C 上一階是 level2 的 A
最後 level2 A 找到 level 1 有 A, 但 A 的 level2 是空白
最後得到 level1 E 是屬於 level1 A 的階層

如果用圖示是這樣
L1 L2
E -> B
B -> C
C -> A
A -> 空白 -> 答案
我想用 sql 語法可以一次過, 試了很多都調不出來, 不知道有沒有什麼寫法可解
目前我是用迴圈不斷來查 level 1 的代號 直到沒有 level2 的值才結束!

編輯記錄

你好,

這一段sql示例,或許對你有幫助
http://sqlfiddle.com/#!6/7355f/1

源自於這篇的討論串
https://stackoverflow.com/questions/17676944/finding-a-top-level-parent-in-sql

研究了好一陣子, 語法實在看不懂, 我使用firebird 資料庫, 範例好像是oracle, .不過還是感謝提供!

因為我只有一個資料庫, 非範例有兩個,
所以我用這方法可以稍稍簡化迴圈的麻煩, 只是這段語法被限制階層數量, 沒有辦法動態決定層數(或許有辦法, 但超出我認知能力), 以及出來的結果還必須用迴圈判斷欄位值來決定應該要取那一層的結果
select
a.L1, a.L2, a.LNO,
b.L1, b.L2, b.LNO,
c.L1, c.L2, c.LNO,
d.L1, d.L2, d.LNO
from LEVL a

left join
(
select L1, L2, LNO
from LEVL
) b
on a.L2 = b.L1

left join
(
select L1, L2, LNO
from LEVL
) c
on b.L2 = c.L1

left join
(
select L1, L2, LNO
from LEVL
) d
on c.L2 = d.L1

where a.L1 = 'E'

編輯記錄

===================引 用 pedro 文 章===================
你好,

這一段sql示例,或許對你有幫助
http://sqlfiddle.com/#!6/7355f/1

源自於這篇的討論串
https://stackoverflow.com/questions/17676944/finding-a-top-level-parent-in-sql

學了一招，感謝大大

--建立資料表
CREATE TABLE T_level
(
level1 VARCHAR(1) ,
level2 VARCHAR(1)
);

--寫入資料
INSERT INTO T_level (level1,level2)
VALUES
('A', ''),
('B','C'),
('C','A'),
('D','A'),
('E','B');
--查詢
SELECT * FROM T_level;


WITH T_level_CTE AS (
SELECT level1, level2
FROM T_level WHERE level1 = 'E'
UNION ALL
SELECT A.level1, A.level2

FROM T_level A
INNER JOIN T_level_CTE B ON A.level1 = B.level2
)

SELECT * FROM T_level_CTE

P.D 大大可以試試

測試了提供的語法, 結果會出現 LEVL_CTE  has cyclic dependencies 的sql error , 因為沒接觸過這樣的語法設計, 目前還在求教google中, 看看網路的討論有沒有相關資訊!
我把語法按範例改成這樣
WITH LEVL_CTE AS
(
SELECT L1, L2, LNO
FROM LEVL
UNION ALL
SELECT A.L1, A.L2
FROM LEVL A
INNER JOIN LEVL_CTE B
ON A.L1 = B.L2
)
SELECT L1, L2, LNO FROM LEVL_CTE
WHERE L1 = 'E'
ORDER BY LNO

編輯記錄

我也是看到大大們的討論在學的，好像要在2005以上

看看這個範例，可能比較好懂

https://www.tpisoftware.com/tpu/articleDetails/1706

我也是第一次接觸這樣的語法, 前陣子就花了好幾天仔細研究網路上的討論(不下百篇), 我發現 with的用法好像還很好用
可是現在的問題是, 我使用 firebird 資料庫下with 的語法, 一直出現上面的錯誤,
不管怎麼調整都不行, 很挫折, 網路上也有很多人討論這個錯誤, 但回歸到各人提出的解法都大同小異,
也仔細核對語法中每一個引用都是沒錯的, 所以實在不曉得要如何下去~
===================引 用 chris681016 文 章===================
我也是看到大大們的討論在學的，好像要在2005以上

看看這個範例，可能比較好懂

https://www.tpisoftware.com/tpu/articleDetails/1706

用while漸往上一層找的方式, 底下用sql 2008試執行無誤

-- Test data
create table HierarchyData
(
level2 varchar(20),
level1 varchar(20)
)

INSERT INTO HierarchyData (level2,level1) Values(null,'A')
INSERT INTO HierarchyData (level2,level1) Values('C','B')
INSERT INTO HierarchyData (level2,level1) Values('A','C')
INSERT INTO HierarchyData (level2,level1) Values('C','D')
INSERT INTO HierarchyData (level2,level1) Values('B','E')

--drop table HierarchyData

declare @lastcount int
declare @lastcycle int

Select HierarchyData.Level2, HierarchyData.Level1, 0 as [Cycle] INTO #list
FROM HierarchyData

SET @lastcount = @@rowcount
SET @lastcycle = 0

while @lastcount > 0
BEGIN
INSERT INTO #list
SELECT Members.Level2, Child.Level1 as Level1, @lastcycle 1 as [Cycle]
FROM #list Members
JOIN HierarchyData Child ON Members.level1 = Child.level2
LEFT JOIN #list cycletest ON Members.level2 = cycletest.level2 AND Child.level1 = cycletest.level1
WHERE Members.Cycle = @lastcycle AND NOT (Members.level2 = Child.level1) AND cycletest.level2 is null

SET @lastcount = @@rowcount

SET @lastcycle = @lastcycle 1
END

SELECT top 1 level2, level1 FROM #list
where Level1='E'
ORDER BY level2, level1

--DROP TABLE #list

資料來源
https://stackoverflow.com/questions/2031684/sql-server-query-to-flattening-a-hierarchy-of-records

感謝 pedro 前輩提供很多資料, 讓我又學習到sql 更深一層的用法,
這段我試作在firebird上好像也有狀況,
而提供的語法有些超越原本的設計想法, 原本只想說是否有可能透過Query 的元件下sql 語法就可以完成,
with 的用法我覺得是現在可符合我的需求, 但無奈在firebird 上找不到解決方法, 有查firebird 2.x 有支援這個語法
會繼續找相關的討論, 或許有一天能突破盲點

謝謝!