SQL Server 遞迴

標籤：

SQL Server 沒有類似於Oracle START WITH NAME=‘xx‘ CONNECT BY PRIOR ID=PARENT_ID這樣的語句，但是可以通過自訂標準函數+With語句實現，速度也是杠杠的

 

ALTER FUNCTION  [dbo].[RecursionSysLocation](        -- Add the parameters for the function here    @ParentId nvarchar(36))RETURNS TABLE ASRETURN (    with temp ( [Id], [parentid])    as    (        select Id, ParentId        from SysLocation        where ParentId = @ParentId        union all        select a.Id, a.ParentId        from SysLocation a        inner join temp on a.ParentId = temp.[Id]            )        select s.Id, s.ParentId from SysLocation s where Id=@ParentId    union all    select * from temp)

 

SQL Server 遞迴