如何只查询出来从根到指定层级的所有节点,使用with是查询出全部节点,中间能加逻辑判断吗?还是只能用临时表实现指定层级的查询?
在查询结果中,可以增加level列,然后在最后的where语句中进行过滤。
WITH org AS (
SELECT id, name, pid, 1 AS level
FROM t1
WHERE pid = 'A'
UNION ALL
SELECT t1.id, t1.name, t1.pid, t2.level + 1 AS level
FROM tmp_t1 t1 INNER JOIN org t2
ON t1.pid = t2.id
)
SELECT
*
FROM org
where level <=2