6,129
社区成员
发帖
与我相关
我的任务
分享请教sql2005中的CTE递归用法
一个简单的树形结构 department 表有三列：id, name, parentid
如何使用CTE获得指定id的节点的所有子孙节点？我仿照联机丛书的说明写成以下这个样子，但是没能得到预想的结果。请高人指点！
----------------
WITH temp(ID, Name, parentID, Level)
AS
(
SELECT d.id, d.name, d.parentid, 0 AS Level
FROM department AS d
INNER JOIN department AS childD
ON d.id = childD.parentID
WHERE d.parentid IS NULL
UNION ALL
SELECT d.id, d.name, d.parentid, Level + 1
FROM department AS d
INNER JOIN department AS childD
ON d.id = childD.parentID
INNER JOIN temp AS t
ON d.parentID = t.id
)
SELECT ID, Name, parentID, Level FROM temp where id = xxx
如何使用CTE获得指定id的节点的所有子孙节点？我仿照联机丛书的说明写成以下这个样子，但是没能得到预想的结果。请高人指点！
----------------
WITH temp(ID, Name, parentID, Level)
AS
(
SELECT d.id, d.name, d.parentid, 0 AS Level
FROM department AS d
INNER JOIN department AS childD
ON d.id = childD.parentID
WHERE d.parentid IS NULL
UNION ALL
SELECT d.id, d.name, d.parentid, Level + 1
FROM department AS d
INNER JOIN department AS childD
ON d.id = childD.parentID
INNER JOIN temp AS t
ON d.parentID = t.id
)
SELECT ID, Name, parentID, Level FROM temp where id = xxx
...全文
请发表友善的回复…
发表回复
iamtrueman 2007-08-13
- 打赏
- 举报
多谢楼上各位，特别是邹建老大。鉴于邹建的分已经很多，我就分点给另两位兄弟吧 ：）
rubiki 2007-08-12
- 打赏
- 举报
再楼上的SQL看起来真的好标准，就像教科书一样工整，佩服
rubiki 2007-08-12
- 打赏
- 举报
在我看来所有子孙节点指的是cte树中
不包括自身根结点的所有其他结点
另外，楼上的SQL好像一直有语法错误，没用试过吧
楼主的目的好像就是找所有子孙节点，他找的SQL也是随便找的，可能就不用照写了
不包括自身根结点的所有其他结点
另外，楼上的SQL好像一直有语法错误，没用试过吧
楼主的目的好像就是找所有子孙节点，他找的SQL也是随便找的，可能就不用照写了
zjcxc 2007-08-12
- 打赏
- 举报
指定节点的子结点的写法, 是把条件写在 CTE 中
WITH temp(ID, Name, parentID, Level)
(
SELECT d.id, d.name, d.parentid, 0 AS Level
FROM department AS d
WHERE id = 要查询的结点 id
-- 上面这个查询是初始化用的, 所以只需要查询最顶一层的结点
UNION ALL -- 得到id的子结点
SELECT d.id, d.name, d.parentid, Level + 1
FROM department d
JOIN temp AS childD
ON d.parentID = childD.id
-- 上面这个查询才是用来递归的, 它与 CTE 上一次的结果 JOIN , 得到上一次结果的子层结点
)
SELECT ID, Name, parentID, Level FROM temp where id = xxx
WITH temp(ID, Name, parentID, Level)
(
SELECT d.id, d.name, d.parentid, 0 AS Level
FROM department AS d
WHERE id = 要查询的结点 id
-- 上面这个查询是初始化用的, 所以只需要查询最顶一层的结点
UNION ALL -- 得到id的子结点
SELECT d.id, d.name, d.parentid, Level + 1
FROM department d
JOIN temp AS childD
ON d.parentID = childD.id
-- 上面这个查询才是用来递归的, 它与 CTE 上一次的结果 JOIN , 得到上一次结果的子层结点
)
SELECT ID, Name, parentID, Level FROM temp where id = xxx
zjcxc 2007-08-12
- 打赏
- 举报
zjcxc(邹建) 这个：
SELECT ID, Name, parentID, Level FROM temp where id = xxx
----------------------------------------------------------------
这是楼主自己要加的条件啊, 由于不知道其的目的, 当然照写, 我只是保证CTE是正确的递归而已
SELECT ID, Name, parentID, Level FROM temp where id = xxx
----------------------------------------------------------------
这是楼主自己要加的条件啊, 由于不知道其的目的, 当然照写, 我只是保证CTE是正确的递归而已
zjcxc 2007-08-12
- 打赏
- 举报
确实没有调试过, 只是把楼主的改改而已, 少了 AS 关键字
WITH temp (ID, Name, parentID, Level)
AS(
SELECT d.id, d.name, d.parentid, 0 AS Level
FROM department AS d
WHERE id = 1 --要查询的结点 id
-- 上面这个查询是初始化用的, 所以只需要查询最顶一层的结点
UNION ALL -- 得到id的子结点
SELECT d.id, d.name, d.parentid, Level + 1
FROM department d
JOIN temp AS childD
ON d.parentID = childD.id
-- 上面这个查询才是用来递归的, 它与 CTE 上一次的结果 JOIN , 得到上一次结果的子层结点
)
SELECT ID, Name, parentID, Level FROM temp where id = xxx
WITH temp (ID, Name, parentID, Level)
AS(
SELECT d.id, d.name, d.parentid, 0 AS Level
FROM department AS d
WHERE id = 1 --要查询的结点 id
-- 上面这个查询是初始化用的, 所以只需要查询最顶一层的结点
UNION ALL -- 得到id的子结点
SELECT d.id, d.name, d.parentid, Level + 1
FROM department d
JOIN temp AS childD
ON d.parentID = childD.id
-- 上面这个查询才是用来递归的, 它与 CTE 上一次的结果 JOIN , 得到上一次结果的子层结点
)
SELECT ID, Name, parentID, Level FROM temp where id = xxx
rubiki 2007-08-11
- 打赏
- 举报
zjcxc(邹建) 这个：
SELECT ID, Name, parentID, Level FROM temp where id = xxx
怎么看也只有一条记录返回吧？没具体试过
如何使用CTE获得指定id的节点的所有子孙节点？
SELECT ID, Name, parentID, Level FROM temp where id = xxx
怎么看也只有一条记录返回吧？没具体试过
如何使用CTE获得指定id的节点的所有子孙节点？
rubiki 2007-08-11
- 打赏
- 举报
;with temp as
(select id,name,parentid,0 level
from department
where id=XX
union all
select a.id,a.name,a.parentid,level+1 level
from department a
join temp t on a.parentid=t.id
)
select * from temp where id<>XX
(select id,name,parentid,0 level
from department
where id=XX
union all
select a.id,a.name,a.parentid,level+1 level
from department a
join temp t on a.parentid=t.id
)
select * from temp where id<>XX
rubiki 2007-08-11
- 打赏
- 举报
还是我那个简单些
其实都差不多
其实都差不多
Limpire 2007-08-11
- 打赏
- 举报
Mark
zjcxc 2007-08-11
- 打赏
- 举报
ashzs((可以包含中文字符)) 的把递归部分把引用的数据写反了
zjcxc 2007-08-11
- 打赏
- 举报
WITH temp(ID, Name, parentID, Level)
(
SELECT d.id, d.name, d.parentid, 0 AS Level
FROM department AS d
WHERE d.parentid IS NULL
-- 上面这个查询是初始化用的, 所以只需要查询最顶一层的结点
UNION ALL
SELECT d.id, d.name, d.parentid, Level + 1
FROM department d
JOIN temp AS childD
ON d.parentID = childD.id
-- 上面这个查询才是用来递归的, 它与 CTE 上一次的结果 JOIN , 得到上一次结果的子层结点
)
SELECT ID, Name, parentID, Level FROM temp where id = xxx
(
SELECT d.id, d.name, d.parentid, 0 AS Level
FROM department AS d
WHERE d.parentid IS NULL
-- 上面这个查询是初始化用的, 所以只需要查询最顶一层的结点
UNION ALL
SELECT d.id, d.name, d.parentid, Level + 1
FROM department d
JOIN temp AS childD
ON d.parentID = childD.id
-- 上面这个查询才是用来递归的, 它与 CTE 上一次的结果 JOIN , 得到上一次结果的子层结点
)
SELECT ID, Name, parentID, Level FROM temp where id = xxx
rubiki 2007-08-11
- 打赏
- 举报
;with temp as
(select id,name,parentid,0 level
from department
where id=1
union all
select a.id,a.name,a.parentid,level+1 level
from department a
join temp t on .parentid=t.id
)
select * from temp where id<>1
(select id,name,parentid,0 level
from department
where id=1
union all
select a.id,a.name,a.parentid,level+1 level
from department a
join temp t on .parentid=t.id
)
select * from temp where id<>1
iamtrueman 2007-07-30
- 打赏
- 举报
楼上的兄弟，你给出的语句会发生无限递归
fa_ge 2007-07-28
- 打赏
- 举报
幫頂下
ashzs 2007-07-28
- 打赏
- 举报
WITH temp(ID, Name, parentID, Level)
(
SELECT d.id, d.name, d.parentid, 0 AS Level
FROM department AS d
WHERE d.parentid IS NULL
UNION ALL
SELECT d.id, d.name, d.parentid, Level + 1
FROM temp d
JOIN department AS childD
ON d.id = childD.parentID
)
SELECT ID, Name, parentID, Level FROM temp where id = xxx
(
SELECT d.id, d.name, d.parentid, 0 AS Level
FROM department AS d
WHERE d.parentid IS NULL
UNION ALL
SELECT d.id, d.name, d.parentid, Level + 1
FROM temp d
JOIN department AS childD
ON d.id = childD.parentID
)
SELECT ID, Name, parentID, Level FROM temp where id = xxx
