SQL query root node

/*Title: Query the function Aixinjueluo for the specified node and all its parent nodes. Yu Hua (18 years of rain, keep the iceberg Snow lotus flower Open) Time: 2008-05-12 location: Shenzhen, Guangdong*/CREATE TABLE TB (ID varchar (3), PID varchar (3), Name varchar (Ten) ) insert into TB values ('001',NULL,'Guangdong province') insert into TB values ('002','001','Guangzhou') insert into TB values ('003','001','Shenzhen') insert into TB values ('004','002','Tianhe District') insert into TB values ('005','003','Luohu District') insert into TB values ('006','003','Futian District') insert into TB values ('007','003','Baoan District') insert into TB values ('008','007','West Township') insert into TB values ('009','007','Longhua Town') insert into TB values ('010','007','Songgang Town') Go--queries the function that specifies the node and all its parent nodes, create function f_pid (@id varchar (3)) returns @t_level table (ID varchar (3)) asbegin INSERT INTO @t_levelSelect@idSelect@id = pid fromTbwhereid = @id and PID isNotNULL   while@ @ROWCOUNT >0begin INSERT INTO @t_levelSelect@idSelect@id = pid fromTbwhereid = @id and PID isNotNULLEndreturnEndgo--Call function Query 002 (Guangzhou city) and all its parent nodesSelectA.* fromTB A, F_pid ('002') bwherea.ID =b.id ORDER BY a.ID/*ID PID name------------------001 NULL Guangdong Province 002 001 Guangzhou City (the number of rows affected is 2 rows)*/--Call function Query 003 (SHENZHEN) and all its parent nodesSelectA.* fromTB A, F_pid ('003') bwherea.ID =b.id ORDER BY a.ID/*ID PID name------------------001 NULL Guangdong Province 003 001 Shenzhen (the number of rows affected is 2 rows)*/--Call function Query 008 (West Township) and all its parent nodesSelectA.* fromTB A, F_pid ('008') bwherea.ID =b.id ORDER BY a.ID/*ID PID name------------------001 NULL Guangdong 003 001 shenzhen 007 003 Bao ' an district 008 007 West Township (the number of rows affected is 4 rows)*/drop table Tbdrop function F_pid

http://bbs.csdn.net/topics/300112071

SQL query root node