一个sql 递归查询,该怎么处理

一个sql 递归查询
现在有这么一张表

ID 父ID IP
1 0 127.0.0.1
2 0 127.0.0.2
3 0 127.0.0.5
4 1  
5 1
6 2
7 3
8 7  
9 4

想通过查询 变成这样

1 0 127.0.0.1
2 0 127.0.0.2
3 0 127.0.0.5
4 1 127.0.0.1
5 1 127.0.0.1
6 2 127.0.0.2
7 3 127.0.0.5
8 7 127.0.0.5
9 4 127.0.0.1


[解决办法]

SQL code

--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL   DROP TABLE [tb]GO CREATE TABLE [tb]  (    [ID] INT ,    [父ID] INT ,    [IP] VARCHAR(9)  )INSERT  [tb]        SELECT  1, 0, '127.0.0.1'        UNION ALL        SELECT  2, 0, '127.0.0.2'        UNION ALL        SELECT  3, 0, '127.0.0.5'        UNION ALL        SELECT  4, 1, NULL        UNION ALL        SELECT  5, 1, NULL        UNION ALL        SELECT  6, 2, NULL        UNION ALL        SELECT  7, 3, NULL        UNION ALL        SELECT  8, 7, NULL        UNION ALL        SELECT  9, 4, NULL--------------开始查询--------------------------;WITH  t AS ( SELECT *             FROM   [tb]             WHERE  [父ID] = 0             UNION ALL             SELECT b.id, b.[父ID], t.[IP]             FROM   t ,                    [tb] b             WHERE  t.[ID] = b.[父ID]           )  SELECT  *  FROM    t----------------结果----------------------------/* ID          父ID         IP----------- ----------- ---------1           0           127.0.0.12           0           127.0.0.23           0           127.0.0.57           3           127.0.0.58           7           127.0.0.56           2           127.0.0.24           1           127.0.0.15           1           127.0.0.19           4           127.0.0.1(9 行受影响)*/
[解决办法]
SQL code
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([ID] int,[FID] int,[IP] varchar(9))insert [test]select 1,0,'127.0.0.1' union allselect 2,0,'127.0.0.2' union allselect 3,0,'127.0.0.5' union allselect 4,1,null union allselect 5,1,null union allselect 6,2,null union allselect 7,3,null union allselect 8,7,null union allselect 9,4,nullwith tas(select * from test where [FID]=0union allselect a.[ID],a.[FID],b.[IP] from test ainner join t b on a.FID=b.ID)select * from torder by 1/*ID    FID    IP--------------------------------------1    0    127.0.0.12    0    127.0.0.23    0    127.0.0.54    1    127.0.0.15    1    127.0.0.16    2    127.0.0.27    3    127.0.0.58    7    127.0.0.59    4    127.0.0.1*/