微軟的MSSQL也有遞迴功能,寫法如下,看起來有點奇怪,需要適應一下。
WITH AppCTE([app_id], [app_pid], [app_name]) AS
(
-- Anchor Query
SELECT [app_id], [app_pid], [app_name] FROM [application] WHERE [app_id] = 128
UNION ALL
-- Recursive Query
SELECT m.[app_id], m.[app_pid], m.[app_name] FROM [application] m INNER JOIN AppCTE r ON m.[app_id] = r.[app_pid]
)
SELECT [app_name] FROM AppCTE ORDER BY app_id ASC