我有一个使用以下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中可用。