如何在单个查询中查找递归数据。 [英] how to find recursion data in single query.
本文介绍了如何在单个查询中查找递归数据。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张数据表:
ID Case_ID Related_Case_ID
1 11 22
2 22 33
3 05 47
4 89 33
5 33 44
6 67 56
现在我想查找case_id = 11的所有相关案例结果如:
ID Case_ID Related_Case_ID
1 11 22
2 22 33
5 33 44
任何建议真的很感激!
解决方案
请参阅此
http://technet.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx [ ^ ]
结果:
创建 table ## table1(id bigint identity (< span class =code-digit> 1 , 1 ),case_id bigint ,Related_case_id bigint )
insert into ## table1(case_id,Related_case_id)值( 11 , 22 )
insert into ## table1(case_id, Related_case_id)值( 22 , 33 )
插入 进入 ## table1(case_id,Related_case_id)值( 05 , 47 )
insert into ## table1(case_id,Related_case_id)值( 89 , 33 )
插入 进入 ## table1(case_id,Related_case_id)值 ( 33 , 44 )
insert into ## table1(case_id,Related_case_id) values ( 67 , 56 )
选择 *来自## table1
cte(id,pid)
as
(选择 case_id,Related_case_id 来自 ## table 1 其中 case_id = 11
union all
选择 a.pid,t.Related_case_id 来自 cte as a inner join ## table1 as t on a.pid = t.case_id
)
选择 *来自cte
WITH q AS
(
SELECT *
FROM Tbl1
WHERE Case_ID = 11
UNION ALL
S ELECT m。*
FROM Tbl1 m
JOIN q
ON m.Case_ID = q.Related_Case_ID
)
SELECT *
FROM q
Hi I have a table with data :
ID Case_ID Related_Case_ID
1 11 22
2 22 33
3 05 47
4 89 33
5 33 44
6 67 56
now I want to find all the related case of case_id=11 the result like :
ID Case_ID Related_Case_ID
1 11 22
2 22 33
5 33 44
Any suggestion really appreciated!
解决方案
Refer this
http://technet.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx[^]
Result :
create table ##table1 (id bigint identity(1,1),case_id bigint,Related_case_id bigint) insert into ##table1(case_id ,Related_case_id) values(11,22) insert into ##table1(case_id ,Related_case_id) values(22,33) insert into ##table1(case_id ,Related_case_id) values(05,47) insert into ##table1(case_id ,Related_case_id) values(89,33) insert into ##table1(case_id ,Related_case_id) values(33,44) insert into ##table1(case_id ,Related_case_id) values(67,56) select *From ##table1 with cte(id , pid) as (select case_id,Related_case_id from ##table1 where case_id=11 union all select a.pid ,t.Related_case_id from cte as a inner join ##table1 as t on a.pid = t.case_id ) select *from cte
WITH q AS ( SELECT * FROM Tbl1 WHERE Case_ID=11 UNION ALL SELECT m.* FROM Tbl1 m JOIN q ON m.Case_ID = q.Related_Case_ID ) SELECT * FROM q
这篇关于如何在单个查询中查找递归数据。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文