SQL Server recursive query

Have the following data sheet

If we want to query all the child nodes of the data with ID 003 We can use the CTE recursive query to complete ...

[SQL]View Plaincopyprint?

2. If object_id (' TB ',' N ') is not null
4. drop table TB;
10. Create table TB (ID varchar (3), PID varchar (3), name varchar (10));
12. INSERT INTO TB values (' 001 ', null, ' Guangdong province ');
14. INSERT INTO TB values (' 002 ', ' 001 ', ' Guangzhou ');
16. INSERT INTO TB values (' 003 ', ' 001 ', ' Shenzhen ');
18. INSERT INTO TB values (' 004 ', ' 002 ', ' Tianhe District ');
20. INSERT INTO TB values (' 005 ', ' 003 ', ' Luohu ');
22. INSERT INTO TB values (' 006 ', ' 003 ', ' Futian District ');
24. INSERT INTO TB values (' 007 ', ' 003 ', ' bao ' an ');
26. INSERT INTO TB values (' 008 ', ' 007 ', ' West Township ');
28. INSERT INTO TB values (' 009 ', ' 007 ', ' Longhua Town ');
30. INSERT INTO TB values (' 010 ', ' 007 ', ' Songgang ');
34. SELECT * from TB;
38. With CTE as
40. (
42. Select A.id,a.name,a.pid from TB a where id=' 003 '
44. Union All
46. Select K.id,k.name,k.pid from TB K inner join CTE C on c.id = k.pid
48. )SELECT * from CTE

The query results are as follows:
003 Shenzhen City 001
005 Luohu District 003
006 Futian District 003
007 Baoan District 003
008 West Township 007
009 Longhua Town 007
010 Songgang Town 007

Transferred from: http://blog.csdn.net/myxx520/article/details/6922682

SQL Server recursive query (GO)