select id,val,connect_by_root(pid) pid from T START WITH val='root' or pid='/' connect by prior id=pid 不过你给的结果是不是有问题,规则不一样 id为1的所有子节点,获取的根节点是id为1这条记录的pid字段 id为2的所有子节点,获取的根节点是id为2这条记录的id字段 不知道是写错了,还是要求就这样, 如果要求是这样的话,把connect_by_root(pid)用decode处理下 改为connect_by_root(decode(……))
--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
就是‘/’和val字段的值为‘root’都算根节点。
1 -> 3 -> 5 不就是多层吗?递归正确的啊!
8#的方法貌似是sqlserver的写法,oracle树形查询使用connect by start with即可实现
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' 这个不会把数据写死了么?
我已经加注释了。 Oracle 反正也有递归,总能转成对应的语句。
sqlserver有没有别的写法,这个查询结果我想作为一个基础表还需要和别的表进行关联
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转换一下,该怎么转呢?
这个怎么转换成mysql的语法呢?mysql好像不支持with as
楼主你想多了。每种数据库都是不一样的。 mysql有递归吗?没递归是没法直接套用的。
但是mysql的情况下针对这种情况该怎么处理呢,难倒mysql不能处理吗?
无递归语句的数据库,用临时表实现递归,还是以SQL Server举例。 DECLARE @level intSET @level = 1SELECT *, @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 ENDSELECT * FROM #r ORDER BY id
from T
START WITH val='root' or pid='/'
connect by prior id=pid
不过你给的结果是不是有问题,规则不一样
id为1的所有子节点,获取的根节点是id为1这条记录的pid字段
id为2的所有子节点,获取的根节点是id为2这条记录的id字段
不知道是写错了,还是要求就这样,
如果要求是这样的话,把connect_by_root(pid)用decode处理下
改为connect_by_root(decode(……))
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
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'
这个不会把数据写死了么?
Oracle 反正也有递归,总能转成对应的语句。
建议百度下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有递归吗?没递归是没法直接套用的。
DECLARE @level intSET @level = 1SELECT *, @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
ENDSELECT * FROM #r ORDER BY id