sql server中的父子级联查询，类似于Oracle的connect by_飘云羽逸_新浪博客

CREATE TABLE (

id INT PRIMARY KEY,

name VARCHAR (20),

pid INT

)

 

insert into guest.test1 values(1,'dq', NULL);

insert into guest.test1 values(2,'家电',1);

insert into guest.test1 values(3,'冰箱',2);

insert into guest.test1 values(4,'洗衣机',2);

insert into guest.test1 values(5,'电脑',1);

insert into guest.test1 values(6,'笔记本',5);

insert into guest.test1 values(7,'平板',5);

insert into guest.test1 values(8,'组装机',7);

insert into guest.test1 values(9,'品牌机',7);

-- sql server子查询

WITH subqry (id, NAME, pid) AS (

SELECT id, NAME, pid FROM guest.test1 WHERE id = 5

UNION ALL

SELECT guest.test1.id, guest.test1.NAME, guest.test1.pid

FROM guest.test1, subqry

WHERE guest.test1.pid = subqry.id

) SELECT * FROM subqry;