sql递归查询语句，查询出根节点对应的所有子节点

[fengyun595]

表样例：
id val pid
1 aa /
2 root /
3 cc 1
4 dd 1
5 ee 3
6 ff 2
7 gg 6



val值为root 或pid为‘/’都视为根节点，查询出所有节点对应的根节点呢？查询结果如下所示：

查询结果
id val pid
1 aa /
2 root /
3 cc /
4 dd /
5 ee /
6 ff 2
7 gg 2

[huangdh12]

/这个 是一个节点吗？ 如果是 应该要在表里面有这条记录的吧？ 这样找下去，所有的根节点 都会 是 /才对。

[刘树旺great]

没看懂，看来我还是很菜啊

[Tiger_Zhao]

--SQL Server
WITH table1(id,val,pid) AS (
    SELECT '1','aa','/' UNION ALL
    SELECT '2','root','/' UNION ALL
    SELECT '3','cc','1' UNION ALL
    SELECT '4','dd','1' UNION ALL
    SELECT '5','ee','3' UNION ALL
    SELECT '6','ff','2' UNION ALL
    SELECT '7','gg','6'
)
,r AS (
    SELECT * FROM table1 WHERE val='root' OR pid='/'
    UNION ALL
    SELECT t.id,t.val,
           CASE WHEN r.val='root' THEN t.pid ELSE r.pid END
      FROM r
      JOIN table1 t
        ON r.id =  t.pid
)
SELECT * FROM r ORDER BY id

id   val  pid
---- ---- ----
1    aa   /
2    root /
3    cc   /
4    dd   /
5    ee   /
6    ff   2
7    gg   2

[fengyun595]

引用 7 楼 huangdh12 的回复:

/这个 是一个节点吗？ 如果是 应该要在表里面有这条记录的吧？ 这样找下去，所有的根节点 都会 是 /才对。

就是‘/’和val字段的值为‘root’都算根节点。

[fengyun595]

引用 8 楼 Tiger_Zhao 的回复:

--SQL Server
WITH table1(id,val,pid) AS (
    SELECT '1','aa','/' UNION ALL
    SELECT '2','root','/' UNION ALL
    SELECT '3','cc','1' UNION ALL
    SELECT '4','dd','1' UNION ALL
    SELECT '5','ee','3' UNION ALL
    SELECT '6','ff','2' UNION ALL
    SELECT '7','gg','6'
)
,r AS (
    SELECT * FROM table1 WHERE val='root' OR pid='/'
    UNION ALL
    SELECT t.id,t.val,
           CASE WHEN r.val='root' THEN t.pid ELSE r.pid END
      FROM r
      JOIN table1 t
        ON r.id =  t.pid
)
SELECT * FROM r ORDER BY id

id   val  pid
---- ---- ----
1    aa   /
2    root /
3    cc   /
4    dd   /
5    ee   /
6    ff   2
7    gg   2

这种写法是不是限定死了？当有多层的时候就不行吧？

[Tiger_Zhao]

1 -> 3 -> 5 不就是多层吗？递归正确的啊！

[bw555]

8#的方法貌似是sqlserver的写法，oracle树形查询使用connect by start with即可实现

[fengyun595]

引用 11 楼 Tiger_Zhao 的回复:

1 -> 3 -> 5 不就是多层吗？递归正确的啊！

SELECT '1','aa','/' UNION ALL SELECT '2','root','/' UNION ALL SELECT '3','cc','1' UNION ALL SELECT '4','dd','1' UNION ALL SELECT '5','ee','3' UNION ALL SELECT '6','ff','2' UNION ALL SELECT '7','gg','6' 这个不会把数据写死了么？

[Tiger_Zhao]

我已经加注释了。
Oracle 反正也有递归，总能转成对应的语句。

[fengyun595]

引用 14 楼 Tiger_Zhao 的回复:

我已经加注释了。 Oracle 反正也有递归，总能转成对应的语句。

sqlserver有没有别的写法，这个查询结果我想作为一个基础表还需要和别的表进行关联

[fengyun595]

引用 16 楼 bw555 的回复:

8楼的语句前面是构造数据测试用的，你实际执行时把这部分去掉就行了，8楼没有你的数据环境，只能采用这种方法测试 建议百度下with的用法

--SQL Server
WITH r AS (
    SELECT * FROM table1 WHERE val='root' OR pid='/'
    UNION ALL
    SELECT t.id,t.val,
           CASE WHEN r.val='root' THEN t.pid ELSE r.pid END
      FROM r
      JOIN table1 t
        ON r.id =  t.pid
)
SELECT * FROM r ORDER BY id

这个怎么转换成mysql的语法呢？mysql好像不支持with as

[bw555]

8楼的语句前面是构造数据测试用的，你实际执行时把这部分去掉就行了，8楼没有你的数据环境，只能采用这种方法测试 建议百度下with的用法

--SQL Server
WITH r AS (
    SELECT * FROM table1 WHERE val='root' OR pid='/'
    UNION ALL
    SELECT t.id,t.val,
           CASE WHEN r.val='root' THEN t.pid ELSE r.pid END
      FROM r
      JOIN table1 t
        ON r.id =  t.pid
)
SELECT * FROM r ORDER BY id

[fengyun595]

引用 16 楼 bw555 的回复:

8楼的语句前面是构造数据测试用的，你实际执行时把这部分去掉就行了，8楼没有你的数据环境，只能采用这种方法测试 建议百度下with的用法

--SQL Server
WITH r AS (
    SELECT * FROM table1 WHERE val='root' OR pid='/'
    UNION ALL
    SELECT t.id,t.val,
           CASE WHEN r.val='root' THEN t.pid ELSE r.pid END
      FROM r
      JOIN table1 t
        ON r.id =  t.pid
)
SELECT * FROM r ORDER BY id

这个语句我怎么作为一个基本条件查询呢？比如select b.dn as dn from table a,(with.........上面的sql语句)b,where a.pid=b.pid,这种场景好像不能这么用，我想把with转换一下，该怎么转呢？

[Tiger_Zhao]

用 row_count() 代替 @@ROWCOUNT，其他的都可以翻译过去。

[Tiger_Zhao]

楼主你想多了。每种数据库都是不一样的。
mysql有递归吗？没递归是没法直接套用的。

[fengyun595]

引用 19 楼 Tiger_Zhao 的回复:

楼主你想多了。每种数据库都是不一样的。 mysql有递归吗？没递归是没法直接套用的。

但是mysql的情况下针对这种情况该怎么处理呢，难倒mysql不能处理吗？

[Tiger_Zhao]

无递归语句的数据库，用临时表实现递归，还是以SQL Server举例。

DECLARE @level int

SET @level = 1

SELECT *, @level level
  INTO #r
  FROM table1
 WHERE val='root' OR pid='/'

WHILE (@@ROWCOUNT <> 0)
BEGIN
    SET @level = @level+1

    INSERT INTO #r
    SELECT t.id,t.val,
           CASE WHEN r.val='root' THEN t.pid ELSE r.pid END,
           @level
      FROM #r r
      JOIN table1 t
        ON r.id =  t.pid
     WHERE r.level = @level-1
END

SELECT * FROM #r ORDER BY id

[fengyun595]

引用 21 楼 Tiger_Zhao 的回复:

无递归语句的数据库，用临时表实现递归，还是以SQL Server举例。

DECLARE @level int

SET @level = 1

SELECT *, @level level
  INTO #r
  FROM table1
 WHERE val='root' OR pid='/'

WHILE (@@ROWCOUNT <> 0)
BEGIN
    SET @level = @level+1

    INSERT INTO #r
    SELECT t.id,t.val,
           CASE WHEN r.val='root' THEN t.pid ELSE r.pid END,
           @level
      FROM #r r
      JOIN table1 t
        ON r.id =  t.pid
     WHERE r.level = @level-1
END

SELECT * FROM #r ORDER BY id

mysql也可以用这种临时表做递归么？mysql怎么写呢？

[fengyun595]

引用 23 楼 Tiger_Zhao 的回复:

用 row_count() 代替 @@ROWCOUNT，其他的都可以翻译过去。

大神，我自己试了下翻译不通，还请帮忙翻一下吧，帮人帮到底吧。

[Tiger_Zhao]

我没mysql，怎么翻译？