MySQL 根据下一个和上一个项目对项目进行排序



我有一个使用以下SQL代码创建的项目表。

CREATE TABLE `items` (
`id` INT (11) NOT NULL AUTO_INCREMENT
,`Contents` VARCHAR(256) NOT NULL
,`Set` INT (11) DEFAULT NULL
,`Nxt` INT (11) DEFAULT NULL
,`Prev` INT (11) DEFAULT NULL
,PRIMARY KEY (`id`)
,KEY `Nxt`(`Prev`)
,KEY `Prev`(`Nxt`)
,CONSTRAINT `items_ibfk_1` FOREIGN KEY (`Nxt`) REFERENCES `items`(`id`)
,CONSTRAINT `items_ibfk_2` FOREIGN KEY (`Prev`) REFERENCES `items`(`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

正如你所看到的,每个项目引用它都是上一个项目,下一个项目也是相关的,我在那里有以下数据。

Id      Contents    Set     Prev        Nxt
1       dakhd       1       NULL        4
2       234         2       7           6
3       fwer4w5     1       4           8
4       f34w5       1       1           3
5       234d233     2       NULL        7
6       1234        2       2           NULL
7       324         2       5           2
8       qw4         1       3           NULL

如何编写查询以便按 Set 对它们进行排序,然后在"上一个"列中获取带有 NULL 的项目,然后以接下来的每个项目为例:

Id      Contents    Set     Prev        Nxt
1       dakhd       1       NULL        4
4       f34w5       1       1           3
3       fwer4w5     1       4           8
8       qw4         1       3           NULL
5       234d233     2       NULL        7
7       324         2       5           2
2       234         2       7           6
6       1234        2       2           NULL

我有以下查询,将按顺序对它们进行排序。 设置列 但是我不确定从哪里开始订购项目

select * from `items` order by `Set`;

您可以使用case when,假设id总是大于 0,则以下内容应该有效:

select * 
from `items` 
order by `Set`
,case when `Prev` is null then -1 when `Nxt` is null then 1000000 else `Nxt` end
;

我认为您确实需要一个递归查询:

with recursive cte as (
select i.*, 1 lvl from items where prev is null
union all
select i.*, c.lvl + 1
from cte c
inner join items i on i.prev = c.id
)
select * from cte order by set, lvl

with子句选择每个set中的"第一个"项(由prev中的null值指定),然后循环访问依赖关系树,同时保持表示每个节点深度的计数器的最新状态。然后,可以使用该信息对结果集进行排序。

请注意,递归查询仅在MySQL 8.0中可用。

相关内容

  • 没有找到相关文章

最新更新