一个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*/