SQLサーバーで親子の再帰があるテーブルの先祖一覧と子孫一覧を取得するファンクション

Higtyのシステムの作り方

例えば以下のようなテーブルがあるとします。

CREATE TABLE BookPage
([PageId] UNIQUEIDENTIFIER NOT NULL
,[Title] NVARCHAR (128) NOT NULL
,[ParentPageId] UNIQUEIDENTIFIER

,CONSTRAINT BookPage_PrimaryKey PRIMARY KEY CLUSTERED(PageId)
,CONSTRAINT BookPage_Fk_ParentPageId FOREIGN KEY(ParentPageId) REFERENCES [BookPage](PageId) ON UPDATE NO ACTION ON DELETE NO ACTION
)
GO

上記のようなテーブルに対して先祖や子孫を取得したいというシーンはよくあると思います。そのための便利なファンクションの紹介です。


先祖全てを取得するファンクション

DROP FUNCTION IF EXISTS dbo.Fn_BookPage_Ancestor_Get
GO
CREATE FUNCTION Fn_BookPage_Ancestor_Get
(@PageId UNIQUEIDENTIFIER
,@MaxLayerCount INT
)
RETURNS TABLE
AS
RETURN
(
	WITH CTE AS
	(
		SELECT ParentPageId,PageId,0 AS LayerLevel 
		FROM BookPage WITH(nolock) 
		WHERE PageId = @PageId 
		UNION ALL 
		SELECT T2.ParentPageId,T2.PageId,T1.LayerLevel+1 AS LayerLevel 
		FROM CTE AS T1 
		INNER JOIN BookPage AS T2 ON T1.ParentPageId = T2.PageId
		WHERE T1.LayerLevel < @MaxLayerCount
	)
	SELECT * FROM CTE 
	WHERE LayerLevel > 0
)
GO


子孫全てを取得するファンクション

DROP FUNCTION IF EXISTS dbo.Fn_BookPage_Descendant_Get
GO
CREATE FUNCTION Fn_BookPage_Descendant_Get
(@PageId UNIQUEIDENTIFIER
,@MaxLayerCount INT
)
RETURNS TABLE
AS
RETURN
(
	WITH CTE AS
	(
		SELECT ParentPageId,PageId,1 AS LayerLevel 
		FROM BookPage WITH(nolock) 
		WHERE ParentPageId = @PageId 
		UNION ALL
		SELECT T2.ParentPageId,T2.PageId,T1.LayerLevel+1 AS LayerLevel 
		FROM CTE AS T1 
		INNER JOIN BookPage as T2 ON T1.PageId = T2.ParentPageId
		WHERE T1.LayerLevel < @MaxLayerCount
	)
	SELECT * FROM CTE 
)
GO


@MaxLayerCountは無限ループ防止です。

これらのファンクションは以下のように使えます。

SELECT TOP 1 COUNT(*) FROM Fn_BookPage_Descendant_Get(@PageId,100) 
WHERE PageId = @PageId

上記で指定した@PageId以下の子孫を全て取得できます。



親子関係のあるテーブル全てに上記のファンクションを定義しておくと非常に便利です。