问题:根据公共表达式查询出的结果和 其他表做连接查询,不在存储过程中。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,Table1 WHERE read_tree.CID= Table1.ID 根据上面查询出来的集合去和另外1张表做关联查询,上面结果集做为 left join查询 比如
select * from 上面查询结果集 a
left join table2 b on a.cid = b.ID
where .....
请问如何一个SQL 查询语句实现上面要求,谢谢大家!
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 根据上面查询出来的集合去和另外1张表做关联查询,上面结果集做为 left join查询 比如
select * from 上面查询结果集 a
left join table2 b on a.cid = b.ID
where .....
请问如何一个SQL 查询语句实现上面要求,谢谢大家!
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 ....
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,Table1 WHERE read_tree.CID= Table1.ID
)a
LEFT JOIN table2 b on a.cid = b.ID
where .....
2. 多个表做关联查询,其中查询的条件用到上面查询出的结果集ID 比如: ID = select in (上面的结果集ID)
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 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 b.* ,c.*
from b ,c
where b.ID = c.ID and c,mid in (select CID form read_tree where....)
;with cte as
(
select top 1 id from syscolumns
)
select * from sysobjects where id in (select id from cte where 1=1)
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 .....