如何在SQL中基于自关联FK关系实现任务的相对排序？

你需要的其实是拓扑排序——确保依赖任务始终出现在它所依赖的任务之后。考虑到你的技术栈（MySQL 5.6不支持递归CTE，SQLite 3.19.3，且不想用CTE），我给你几个实用的方案，从纯SQL到应用层实现都有，按需选择：

如果你的任务依赖层级不会嵌套太深（比如最多3-4层），可以用多次自连接计算每个任务的「层级」，然后按层级排序。这种方法不需要CTE，兼容MySQL 5.6和SQLite。

MySQL 5.6 版本 #

SELECT 
    t.name
FROM task t
LEFT JOIN task t1 ON t.dep = t1.id
LEFT JOIN task t2 ON t1.dep = t2.id
LEFT JOIN task t3 ON t2.dep = t3.id
ORDER BY 
    -- 层级升序：确保被依赖任务先出现
    (LENGTH(CONCAT_WS(',', t3.id, t2.id, t1.id, t.id)) - LENGTH(REPLACE(CONCAT_WS(',', t3.id, t2.id, t1.id, t.id), ',', ''))) - 1,
    -- 同层级任务的排序规则，这里用name，你可以换成id或其他字段
    t.name;

解释：CONCAT_WS拼接依赖链的ID，通过计算逗号数量得到层级（比如A的链是1，逗号数0，层级0；B的链是1,2，逗号数1，层级1）。按层级升序，同层级按name排序，就能得到符合要求的结果。如果依赖层级更深，继续添加LEFT JOIN t4 ON t3.dep = t4.id并更新CONCAT_WS里的字段即可。

SQLite 版本 #

SQLite的函数语法略有不同，调整后：

SELECT 
    t.name
FROM task t
LEFT JOIN task t1 ON t.dep = t1.id
LEFT JOIN task t2 ON t1.dep = t2.id
LEFT JOIN task t3 ON t2.dep = t3.id
ORDER BY 
    (length(coalesce(t3.id || ',' || t2.id || ',' || t1.id || ',' || t.id, t.id)) - length(replace(coalesce(t3.id || ',' || t2.id || ',' || t1.id || ',' || t.id, t.id), ',', ''))) - 1,
    t.name;

如果你的任务依赖深度不确定，纯SQL的多次自连接就不够灵活了。MySQL 5.6支持存储过程，我们可以用它实现递归拓扑排序：

DELIMITER //
CREATE PROCEDURE GetSortedTasks()
BEGIN
    -- 创建临时表存储排序后的任务（包含层级）
    CREATE TEMPORARY TABLE IF NOT EXISTS sorted_tasks (
        id INT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        depth INT NOT NULL
    );
    
    -- 先插入所有无依赖的任务，层级设为0
    INSERT INTO sorted_tasks
    SELECT id, name, 0 FROM task WHERE dep IS NULL;
    
    -- 循环插入依赖已排序任务的任务，直到没有新任务可插入
    WHILE ROW_COUNT() > 0 DO
        INSERT INTO sorted_tasks
        SELECT 
            t.id, 
            t.name, 
            st.depth + 1
        FROM task t
        JOIN sorted_tasks st ON t.dep = st.id
        WHERE t.id NOT IN (SELECT id FROM sorted_tasks);
    END WHILE;
    
    -- 输出排序后的任务名称
    SELECT name FROM sorted_tasks ORDER BY depth, name;
    
    -- 清理临时表
    DROP TEMPORARY TABLE IF EXISTS sorted_tasks;
END //
DELIMITER ;

调用方式：CALL GetSortedTasks();
这个存储过程会自动处理任意深度的依赖，每次循环插入依赖已排序任务的新任务，直到所有任务都被排序。

如果你不想在数据库层面处理，用Python代码实现拓扑排序会更灵活，而且兼容所有数据库（包括你的SQLite测试环境和MySQL生产环境）。这和你提到的C#用IComparator的思路类似，但更适合处理依赖关系：

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Task(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    dep = db.Column(db.Integer, db.ForeignKey('task.id'))
    # 反向关系：方便获取依赖当前任务的子任务
    dependent_tasks = db.relationship('Task', backref=db.backref('dependency', remote_side=[id]))

def get_sorted_tasks():
    # 获取所有任务
    all_tasks = Task.query.all()
    
    # 构建依赖映射：key是任务ID，value是依赖该任务的子任务列表
    dependency_map = {}
    # 记录每个任务的入度（即依赖的任务数量）
    in_degree = {}
    # 任务ID到对象的映射
    task_map = {}
    
    for task in all_tasks:
        task_map[task.id] = task
        # 无依赖的任务入度为0，否则为1
        in_degree[task.id] = 1 if task.dep is not None else 0
        
        if task.dep is not None:
            if task.dep not in dependency_map:
                dependency_map[task.dep] = []
            dependency_map[task.dep].append(task)
    
    # 初始化队列：先处理所有无依赖的任务
    queue = [task for task in all_tasks if task.dep is None]
    sorted_tasks = []
    
    while queue:
        # 取出队列中的第一个任务（也可以用pop()实现不同的同层级排序）
        current_task = queue.pop(0)
        sorted_tasks.append(current_task)
        
        # 处理依赖当前任务的子任务：将它们的入度减1，入度为0时加入队列
        if current_task.id in dependency_map:
            for dependent in dependency_map[current_task.id]:
                in_degree[dependent.id] -= 1
                if in_degree[dependent.id] == 0:
                    queue.append(dependent)
    
    # 返回排序后的任务名称列表
    return [task.name for task in sorted_tasks]

这个方法用的是Kahn拓扑排序算法，逻辑清晰：先处理无依赖的任务，再依次处理依赖这些任务的子任务，确保所有依赖都被满足后才处理当前任务。你可以调整queue.pop(0)为queue.pop()来改变同层级任务的排序顺序（从FIFO变为LIFO），或者在加入队列时排序，实现自定义的同层级顺序。

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