c*******r
发帖数: 3289 | 1 col1, col2两个column存放人名id, 要求给出任意一个人的id,找出所有和这个人有联
系的人，以及所有其他有联系的人。例如
col1 col2
---- ----
A B
A C
B J
M B
X J
C K
Q A
输入的人名是A, 需要返回
1. COL2 WHERE COL1 = A，输出B和C；
2. COL1 WHERE COL2 = A, 结果加入Q;
3. COL2 WHERE COL1 IN (B,C,Q), 加入结果;
4. COL1 WHERE COL2 IN (B,C,Q)，加入结果；
...
如此类推直至找不到新的ID
这似乎是个递归问题，能写个store procedure吗 | w****w
发帖数: 521 | 2 Something like:
with friend_cte as (
select col1 as col from friend where col2='a'
union
select col2 as col from friend where col1='a'
union
select col1 as col from friend join friend_cte
on friend.col2=friend_cte.col
union
select col2 as col from friend join friend_cte
on friend.col1=friend_cte.col
);
select col from friend_cte; | a*********y
发帖数: 63 | 3 This only works with MS SQL Server, also there is a limitation for number of
recursion for CTE though.
Better to use procedure with while loop. |
|
