sql server递归调用
INSERT loc(dgid,dgname,parentid) VALUES( 1,'河北省',NULL);INSERT loc(dgid,dgname,parentid) VALUES( 2,'石家庄',1) ;
INSERT loc(dgid,dgname,parentid) VALUES( 3,'保定',1) ;
INSERT loc(dgid,dgname,parentid) VALUES( 4,'山西省',NULL) ;
INSERT loc(dgid,dgname,parentid) VALUES( 5,'太原',4) ;
INSERT loc(dgid,dgname,parentid) VALUES( 6,'新华区',2) ;
INSERT loc(dgid,dgname,parentid) VALUES( 7,'北焦村',6) ;
INSERT loc(dgid,dgname,parentid) VALUES( 8,'大郭村',6) ;
INSERT loc(dgid,dgname,parentid) VALUES( 9,'河南省',NULL) ;
INSERT loc(dgid,dgname,parentid) VALUES( 10,'大郭村南',8) ;
INSERT loc(dgid,dgname,parentid) VALUES( 11,'大郭村北',8) ;
INSERT loc(dgid,dgname,parentid) VALUES( 12,'北焦村东',7) ;
INSERT loc(dgid,dgname,parentid) VALUES( 13,'北焦村西',7) ;
INSERT loc(dgid,dgname,parentid) VALUES( 14,'桥东区',3) ;
INSERT loc(dgid,dgname,parentid) VALUES( 15,'桥西区',3);
CREATE PROCEDURE .
AS
BEGIN
WITH locs(dgid,dgname,parentid,loclevel)
AS
(
SELECT dgid,dgname,parentid,0 AS loclevel FROM loc
WHERE parentid IS NULL
UNION ALL
SELECT l.dgid,l.dgname,l.parentid,loclevel+1 FROM loc l
INNER JOIN locs p ON l.parentid=p.dgid
)
SELECT * FROM locs
END
页:
[1]