日期:2014-05-18  浏览次数:20545 次

一个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.1
2           0           127.0.0.2
3           0           127.0.0.5
7           3           127.0.0.5
8           7           127.0.0.5
6           2           127.0.0.2
4           1           127.0.0.1
5           1           127.0.0.1
9           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 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 test where [FID]=0
union all
select a.[ID],a.[FID],b.[IP] from test a
inner join t b on a.FID=b.ID
)
select * from t
order by 1

/*
ID    FID    IP
--------------------------------------
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
*/