目前作法是往上找到最大,再往下展開

with biga as (
	select distinct a.id
	from relate a
	left join relate b on a.id = b.parentid
	left join relate c on c.parentid = b.id
	where a.parentid = ''
)
select * 
from relate a
left join relate b on a.id = b.parentid
left join relate c on b.id = c.parentid
where a.id in (select id from biga)

但如果沒有指定最大階層上層為空,則無法查詢到

INSERT INTO relate (id,parentid)
VALUES ('1', '')

測試資料:

CREATE TABLE relate(
	id nvarchar(20),
	parentid nvarchar(20)
);

INSERT INTO relate (id,parentid)
VALUES ('1', '')
,('1-1', '1'),('1-2', '1')
,('1-1-1', '1-1'),('1-1-2', '1-1')
,('1-2-1', '1-2'),('1-2-2', '1-2')
,('2-1-1', '2-1'),('2-1-2', '2-1')

有沒有辦法輸入"2-1-1"或是"2-1"
就可以從"2-1"往下展開？

假設階層不大於三階