现在有一个需求,
给定的表结构支持单链表数据的存储,每一行有一个next字段指向下一个节点,在只知道根节点id的情况下查询出该链表的所有数据
表结构
create table node
(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(20),
next int
);
该表存储的是多个单链表的数据,已知某个链表的rootId,需要根据rootId递归查询出整个链表的所有节点
初始化数据,插入两条链表的数据
insert into node VALUE (1, '1', 2);
insert into node VALUE (2, '2', 3);
insert into node VALUE (3, '3', 4);
insert into node VALUE (4, '4', 5);
insert into node VALUE (5, '5', 6);
insert into node VALUE (6, '6', 7);
insert into node VALUE (7, '7', 8);
insert into node VALUE (8, '8', null);
insert into node VALUE (111, '111', 222);
insert into node VALUE (222, '222', 333);
insert into node VALUE (333, '333', 444);
insert into node VALUE (444, '444', 555);
insert into node VALUE (555, '555', null);
查询语句如下,查询其中头结点id为111的链表
SELECT id, name, next
FROM node
INNER JOIN (
SELECT 111 AS a # 111为头节点Id
UNION
SELECT @next_id AS a
FROM node,
(SELECT @next_id := 111) n # 111为头节点Id
WHERE id = @next_id
AND @next_id := next
# 其他条件
) ids
ON node.id = ids.a;
查询结果