目的

ナイーブツリーで構成されている多階層テーブルにおいて
任意の階層に所属しているノード群(= 親ノード)に所属している末端ノードの一覧を
親ノードとの関連が分かるように
1クエリで

取得したい。

つまり

親ノードid	末端ノードid
3	20
3	21
3	22
4	25
4	26
4	27

みたいな結果を取得したい。

環境

MySQL 8~

MySQLだと8系以降でしか再帰クエリが使えないため。
他DBMSでも再帰クエリが使えれば同じことはできるはず。多分。

前提のテーブル構造

tableName: naive_tree

id	parent_id	depth	is_leaf
プライマリ	親id	階層	末端ノードならtrue

再帰クエリについて

ざっくり。

-- ここから再帰クエリ。`temporary_table_name`という名前で、hoge, fuga, piyo, ...の構造を持つ一時的なテーブルを作成する
WITH RECURSIVE <temporary_table_name> (<hoge>, <fuga>, <piyo>, ...) AS (
    -- ここから非再帰項
    -- 最初に実行され、再帰項を実行するベースデータを取得する
    SELECT <hoge>, <fuga>, <piyo>, ... -- 上記で定義したものと同じ構造である必要がある
    -- ここまで非再帰項

        UNION ALL -- 非再帰項と再帰項それぞれで取得できた結果をつなげる

    -- ここから再帰項
    -- 非再帰項の取得結果をベースに実行される
    SELECT <hoge>, <fuga>, <piyo>, ... -- 上記で定義したものと同じ構造である必要がある
    FROM <temporary_table_name> WHERE <condition> -- 一時テーブル自身(temporary_table_name)を使う
    -- ここまで再帰項
)
-- ここまで再帰クエリ
SELECT *
FROM temporary_table_name

クエリ

SET @target_parent_depth = 1; -- 階層数を指定

WITH RECURSIVE tree(id, parent_id, depth, is_leaf, target_depth_parent_id) AS (
    -- 指定された階層のレコードを全て取得
    -- 自分自身が親なのでtarget_depth_parent_idは0としておく
    SELECT id, parent_id, depth, is_leaf, 0 as target_depth_parent_id
    FROM naive_tree
    WHERE id IN (
    	SELECT n.id
    	FROM naive_tree n
    	WHERE n.depth = @target_parent_depth
    )
      UNION ALL
    SELECT
    	n2.id,
    	n2.parent_id,
    	n2.depth,
    	n2.is_leaf,
    	(
    		CASE
	    		WHEN n2.depth = @target_parent_depth + 1 THEN n2.parent_id
	    		ELSE t.target_depth_parent_id
    		END
    	) as target_depth_parent_id
    	-- ここがキモ
    	-- JOINされたレコードが非再帰項で取得された階層の「次の」階層だった場合には、JOINされた側のレコードが持つparent_idを使う。
    	-- それ以外のケースでは、JOIN元のレコードのtarget_depth_parent_idをそのまま使う。
    FROM tree t
    INNER JOIN naive_tree n2 ON t.id = n2.parent_id
)
SELECT
	t.id,
	t.target_depth_parent_id
FROM tree t
WHERE t.is_leaf = 1 AND target_depth_parent_id != 0 -- 末端ノードであり、かつ親レコードではないことを条件とする