34,593
社区成员
发帖
与我相关
我的任务
分享
--这个有什么问题,你把CTE当成一个表就行了
;with cte as
(
select top 1 id from syscolumns
)
select * from sysobjects where id in (select id from cte where 1=1)
WITH read_tree(PID,CID)
AS
(
SELECT L.pid PID, l.cid CID
FROM LINK L ,Table1 p where P.ID='036817100700'
UNION ALL
--递归条件
SELECT M.pid,M.cid
FROM LINK AS M
INNER JOIN read_tree
ON M.pid= read_tree.CID
)
select b.* ,c.*
from b ,c
where b.ID = c.ID and c,mid in (select CID form read_tree where....)
select * from
(
WITH read_tree(PID,CID)
AS
(
SELECT L.pid PID, l.cid CID
FROM LINK L ,Table1 p where P.ID='036817100700'
UNION ALL
--递归条件
SELECT M.pid,M.cid
FROM LINK AS M
INNER JOIN read_tree
ON M.pid= read_tree.CID
)
select * from read_tree where ....
)
1. 查询出结果集SQL
WITH read_tree(PID,CID)
AS
(
SELECT L.pid PID, l.cid CID
FROM LINK L ,Table1 p where P.ID='036817100700'
UNION ALL
--递归条件
SELECT M.pid,M.cid
FROM LINK AS M
INNER JOIN read_tree
ON M.pid= read_tree.CID
)
select * from read_tree where ....
2. select b.* ,c.*
from b ,c
where b.ID = c.ID and c,mid in (select CID form read_tree ) //就是把上面的结果集作为子查询来用。
select * from
(
;WITH read_tree(PID,CID)
AS
(
SELECT L.pid PID, l.cid CID
FROM LINK L ,Table1 p where P.ID='036817100700'
UNION ALL
--递归条件
SELECT M.pid,M.cid
FROM LINK AS M
INNER JOIN read_tree
ON M.pid= read_tree.CID
)
SELECT * FROM read_tree,Table1 WHERE read_tree.CID= Table1.ID
)a
LEFT JOIN table2 b on a.cid = b.ID
where .....
WITH read_tree(PID,CID)
AS
(
SELECT L.pid PID, l.cid CID
FROM LINK L ,Table1 p where P.ID='036817100700'
UNION ALL
--递归条件
SELECT M.pid,M.cid
FROM LINK AS M
INNER JOIN read_tree
ON M.pid= read_tree.CID
)
select * from read_tree a left join table2 b on a.cid = b.ID
where ....