如何在OneToOne关联中仅限制父级数量并按指定规则排序？是否可行？

我来帮你实现这两个需求——筛选指定父级的完整组，以及按父级+子级名称的升/降序排序。下面分场景给出SQL语句，同时解释逻辑：

一、筛选单个父级及其所有子级 #

假设你要保留父级John（id=1）的组，我们可以用CTE（公共表表达式）先锁定这个父级，再筛选它和它的子级：

WITH target_parent AS (
    -- 锁定目标父级：这里选name为John的父级，你可以改成其他条件比如id=1
    SELECT id, name AS parent_name 
    FROM your_table 
    WHERE parent_id IS NULL AND name = 'John'
)
SELECT t.*
FROM your_table t
-- 筛选条件：要么是父级本身，要么是该父级的子级
WHERE (t.parent_id IS NULL AND t.id IN (SELECT id FROM target_parent))
   OR t.parent_id IN (SELECT id FROM target_parent);

如果需要动态选择一个父级（比如选名称最大的、或者第一个父级），只需要修改target_parent里的条件：

• 选名称升序第一个父级：WHERE parent_id IS NULL ORDER BY name ASC LIMIT 1
• 选名称降序第一个父级：WHERE parent_id IS NULL ORDER BY name DESC LIMIT 1

二、按父级名称+子级名称排序（升序/降序） #

要实现先按父级名称、再按子级名称排序，我们需要通过自连接获取父级的名称作为排序依据，同时确保父级本身排在其子级的最前面：

升序（ASC）排序版本 #

WITH target_parent AS (
    SELECT id, name AS parent_name 
    FROM your_table 
    WHERE parent_id IS NULL AND name = 'John'
)
SELECT t.*
FROM your_table t
LEFT JOIN target_parent tp ON t.parent_id = tp.id
WHERE (t.parent_id IS NULL AND t.id = tp.id)
   OR t.parent_id = tp.id
ORDER BY 
    -- 先按父级名称升序
    tp.parent_name ASC,
    -- 父级本身排在子级前面，子级按自身名称升序
    CASE WHEN t.parent_id IS NULL THEN '' ELSE t.name END ASC;

执行后会得到你要的升序结果：

id | parent_id | name | #

1 | null | John |
2 | 1 | Agnes |
3 | 1 | Lucy |

降序（DESC）排序版本 #

WITH target_parent AS (
    SELECT id, name AS parent_name 
    FROM your_table 
    WHERE parent_id IS NULL AND name = 'John'
)
SELECT t.*
FROM your_table t
LEFT JOIN target_parent tp ON t.parent_id = tp.id
WHERE (t.parent_id IS NULL AND t.id = tp.id)
   OR t.parent_id = tp.id
ORDER BY 
    -- 先按父级名称降序
    tp.parent_name DESC,
    -- 父级本身排在子级前面，子级按自身名称降序
    CASE WHEN t.parent_id IS NULL THEN 'zzzz' ELSE t.name END DESC;

执行后得到降序结果：

id | parent_id | name | #

1 | null | John |
3 | 1 | Lucy |
2 | 1 | Agnes |

逻辑说明 #

• target_parent CTE的作用是先锁定我们要保留的父级，避免重复写筛选条件，让SQL更清晰。
• ORDER BY里的CASE语句是关键：升序时给父级一个空字符串（比任何子级名称都小），降序时给父级一个大字符串（比如'zzzz'，比任何子级名称都大），这样父级始终排在其子级的最前面，子级再按自身名称排序。

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