日期:2014-05-17 浏览次数:20555 次
SELECT [id]
,[name]
,[nodeid]
,[fnodeid]
FROM [mydb].[dbo].[TEST_TABLE]
--内容
id name nodeid fnodeid
1 成品 000001 000
2 半成品 000002 000
3 原材料 000003 000
01 传感器 000001001 000001
100 SSS 000001001001 000001001
--查询
with temp_table(id, name, nodeid, fnodeid, levell)
as
(
select CAST(ID AS VARCHAR) AS ID, CAST (NAME AS VARCHAR) AS NAME, nodeid, fnodeid, 1 AS LEVELL
from test_table WHERE NODEID = '000001001001'
union all
select CAST(T.ID + VT.ID AS VARCHAR) AS ID, cast (T.NAME + VT.NAME as varchar) AS NAME, T.NODEID, T.FNODEID, VT.LEVELL + 1 AS LEVELL
FROM TEST_TABLE T, TEMP_TABLE VT
WHERE T.NODEID = VT.FNODEID
)
SELECT TOP 1 ID, name FROM TEMP_TABLE ORDER BY LEVELL DESC
--内容
ID name
101100 成品传感器SSS