MySQL使用Union查询层级数据：选2个父节点及各2个子节点

我来帮你搞定这个查询问题！你的需求很明确：选出前2个根节点（Related=0的节点），再为每个根节点获取前2个子节点，最终得到的ID是1、2、3、7、8对吧？

先说说你原来SQL的问题 #

你之前尝试的SQL存在几个关键问题：

• 第二个SELECT里引用的T1没有定义，MySQL会报错找不到这个表
• UNION后的LIMIT是对整个合并后的结果集生效，没法实现每个父节点单独限制子节点数量的需求

方案一：MySQL 8.0+（支持窗口函数） #

如果你的MySQL版本是8.0及以上，用窗口函数ROW_NUMBER()可以很优雅地解决这个问题：

WITH parent_nodes AS (
    -- 先获取前2个根节点，按ID排序（和你的预期结果匹配）
    SELECT Id, Related
    FROM TABLE
    WHERE Related = 0
    ORDER BY Id
    LIMIT 2
),
child_nodes AS (
    -- 给每个根节点的子节点按ID排序并编号
    SELECT t.Id, t.Related,
           ROW_NUMBER() OVER (PARTITION BY t.Related ORDER BY t.Id) AS rn
    FROM TABLE t
    -- 只关联我们选中的根节点
    JOIN parent_nodes p ON t.Related = p.Id
)
-- 合并根节点和每个根节点的前2个子节点
SELECT Id FROM parent_nodes
UNION ALL
SELECT Id FROM child_nodes WHERE rn <= 2
-- 最终按ID排序输出
ORDER BY Id;

这个SQL的逻辑很清晰：

1. 先用CTEparent_nodes筛选出前2个根节点（1和7）
2. 再用child_nodes给每个根节点的子节点编号，只保留前2个
3. 最后合并根节点和符合条件的子节点，得到你要的结果

方案二：兼容MySQL 5.x版本（无窗口函数） #

如果你的MySQL版本比较旧，不支持CTE和窗口函数，可以用用户变量来实现分组编号：

-- 合并根节点和符合条件的子节点
SELECT Id FROM (
    -- 筛选前2个根节点
    SELECT Id, Related
    FROM TABLE
    WHERE Related = 0
    ORDER BY Id
    LIMIT 2
) p
UNION ALL
SELECT Id FROM (
    -- 用变量给每个根节点的子节点编号
    SELECT t.Id, t.Related,
           @rn := IF(@current_parent = t.Related, @rn + 1, 1) AS rn,
           @current_parent := t.Related
    FROM TABLE t
    -- 初始化变量
    JOIN (SELECT @current_parent := 0, @rn := 0) vars
    -- 关联前2个根节点
    JOIN (
        SELECT Id FROM TABLE WHERE Related = 0 ORDER BY Id LIMIT 2
    ) p ON t.Related = p.Id
    -- 按父节点和子节点ID排序，保证编号正确
    ORDER BY t.Related, t.Id
) c WHERE rn <= 2
-- 最终排序
ORDER BY Id;

这个写法用用户变量@current_parent和@rn来模拟分组编号的逻辑，效果和窗口函数一致。

内容的提问来源于stack exchange，提问作者Maciek