如何在递归查询中按变量排序并返回正确的顺序?

Question

Sql Fiddle

我有以下结构和查询

CREATE TABLE test (
id INT NOT NULL,
val varchar(255),
parent INT NULL, 
PRIMARY KEY(id)
);
INSERT INTO test (id ,val ,parent) VALUES
(1 ,'Val_1', NULL),
(2, 'Val_2', 1),
(3, 'Val_3', 1),
(4, 'Val_4', 2),
(5, 'Val_5', 3), 
(6, 'Val_6', 1); 
SET @count = 1;
WITH RECURSIVE cte AS (
SELECT 
id,
val,
parent,
@count
FROM
test
WHERE id = 1
UNION ALL
SELECT
b.id,
b.val,
b.parent,
@count := @count + 1
FROM test b
INNER JOIN cte ON b.parent = cte.id
)
select * from cte order by @count asc;

SET @count = 1;
WITH RECURSIVE cte AS (
SELECT id, val, parent, @count
FROM test
WHERE id = 1
UNION ALL
SELECT b.id, b.val, b.parent, @count := @count + 1
FROM test b
INNER JOIN cte ON b.parent = cte.id
)
select * from cte order by @count asc;

我在表上有一个父子关系，我想根据不同的情况增加一个变量，我已经设法在每个递归中设置一个变量和增量，但是当按变量排序时顺序是错误的。

我知道文档指定:

HAVING, GROUP BY和ORDER BY，当引用的变量是在选择表达式列表中分配的值未按预期工作因为表达式是在客户机上求值的，因此可以使用

但是我真的不明白那是什么意思。

那么，为什么在这种情况下顺序是错误的，是否有一种方法可以按变量排序并使其返回"正确"的顺序?这样我就可以避免为订单创建另一个列。

如果我按父列排序，顺序是1-1-1-2-3，为什么当我试图按@count排序时，它不起作用，这样它们就会按照计数1-2-3-4-5的顺序出现…此外，当多次运行查询并按@count变量排序时，顺序会发生变化。

是否有其他方法可以创建一个不在数据库中的值，以便我可以使用它进行排序?

Answer 1

你的sql非常接近你需要的。

将count重命名为rlevel，因为count是sql关键字…

WITH RECURSIVE cte(id, val, parent, rlevel) AS (
SELECT id, val, parent, 1
FROM test
WHERE id = 1
UNION ALL
SELECT b.id, b.val, b.parent, cte.rlevel + 1
FROM test b
INNER JOIN cte ON b.parent = cte.id
)
select * from cte order by rlevel asc;