mysql - SQL - 如何对彼此相对的行进行排序?
问题描述
我有一张要执行的任务表。每个任务可以有一个依赖项,由一列表示,dep
。该列与id
同一个表的列形成一个FK。该列可以为空,因此任务可以没有依赖关系。我希望能够对任务列表进行排序,以便一个任务总是出现在它所依赖的另一个任务之后。
所以,有一个这样的例子:
id | name | dep
----------------
1 | A | NULL
2 | B | 1
3 | C | 1
4 | D | 2
有效的顺序是A,B,C,D
、A,C,B,D
和A,C,D,B
。这在技术上是部分排序。B
相对于的位置C
并不重要。重要的是“A”在“B”和“C”之前,而“B”在“D”之前。
如果我在 C# 中做这样的事情,我可能会创建一个实现IComparator<T>
接口的类,并根据对象之间的关系返回1
或返回。-1
但似乎 SQL 的ORDER BY
子句不允许这样的事情。
在标准 SQL 中,如何根据 FK 关系对行进行相对排序?
更新:
我正在使用 Flask-SQLAlchemy ( Flask-SQLAlchemy==2.1
and SQLAlchemy==0.9.9
) 连接到数据库。为了测试,我使用内存中的 sqlite db(版本3.19.3
),在生产中我使用 MySQL 5.6。
我曾希望避免使用 CTE,因为我不熟悉它们,而且我的 MySQL 版本不支持它们。
我的 DDL 看起来像这样(MySQL 方言):
CREATE TABLE `task` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`dep` int(11) DEFAULT NULL,
PRIMARY KEY (`id),
CONSTRAINT `task_ibfk_1` FOREIGN KEY (`dep`) REFERENCES `task` (`id`)
);
我最初的计划是像这样对任务进行排序:
SELECT name FROM task JOIN task AS t2 ON (t2.id = task.dep) ORDER BY (???);
但我怀疑这是不可能的。
解决方案
编辑:这是为 Microsoft SQL Server 编写的,它可能无法转移到正在使用的 DBMS OP。我把它留在这里以供参考。
当我们等待您更新您正在使用的 DBMS 时,我会把它扔进戒指中。正如评论者指出的那样,它使用递归 CTE 来根据与父级的关系确定每个任务所处的级别(最终是我们的排序顺序)。我稍微更改了您的输入数据以显示正确排序,因为您的输入数据已经处于理想状态。
WITH Test(id, name, dep) AS
(
SELECT 1, 'A', NULL UNION
SELECT 2, 'B', 4 UNION
SELECT 3, 'C', 2 UNION
SELECT 4, 'D', 1
)
, RecursiveCTE AS
(
SELECT id, name, dep, 0 AS level
FROM Test
WHERE dep IS NULL
UNION ALL
SELECT i.id, i.name, i.dep, level + i.id
FROM Test i
INNER JOIN RecursiveCTE c ON c.id = i.dep
)
SELECT id, name, dep
FROM RecursiveCTE
ORDER BY level;
输出:
id name dep
-- ---- ----
1 A NULL
4 D 1
2 B 4
3 C 2
推荐阅读
- vhdl - 如何在不使用 for 循环的情况下获取 one-hot 编码向量的索引?
- java - 移除回调后如何重用Android-Handler
- excel - 如何在 Excel VBA 中比较两个锯齿状数组
- c# - ValidationAttribute 注入服务,用于不显眼的客户端验证
- java - java中是否有一个函数可以帮助格式化并返回格式化的日期作为输出?
- java - findById() 不适用于 Spring Boot 和 Jpa
- wordpress - NGINX - 匹配所有位置
- c# - 如何在 DataGrid.ContextMenu MVVM 中绑定来自两个源(Datagrid 和 TreeView)的多个 CommandParameter
- postgresql - 如何打开docker卷?
- sql - 有没有办法在没有 Xampp 的情况下使用 phpmyadmin,或者在没有 xampp 的情况下访问数据库之类的东西