DECLARE @A TABLE (id int,name char,pid int)INSERT INTO @A(id,name,pid) SELECT 1,'r',0 UNION ALL SELECT 2,'r',0 UNION ALL SELECT 3,'c',1select *, case when exists(select 1 from @A where pid=t.id) then 1 else 0 end from @A t
DECLARE @A TABLE (id int,name char,pid int)INSERT INTO @A(id,name,pid) SELECT 1,'r',0 UNION ALL SELECT 2,'r',0 UNION ALL SELECT 3,'c',1select * , haschild = (select count(1) from @a where pid = t.id) from @A t/* id name pid haschild ----------- ---- ----------- ----------- 1 r 0 1 2 r 0 0 3 c 1 0(所影响的行数为 3 行) */
;with f as ( select * from tb where name='r' union all select a.* from tb a, f where a.pid=f.id ) select * from f
SELECT 1,'r',0 UNION ALL
SELECT 2,'r',0 UNION ALL
SELECT 3,'c',1select *, case when exists(select 1 from @A where pid=t.id) then 1 else 0 end from @A t
SELECT 1,'r',0 UNION ALL
SELECT 2,'r',0 UNION ALL
SELECT 3,'c',1select * , haschild = (select count(1) from @a where pid = t.id) from @A t/*
id name pid haschild
----------- ---- ----------- -----------
1 r 0 1
2 r 0 0
3 c 1 0(所影响的行数为 3 行)
*/
(
select * from tb where name='r'
union all
select a.* from tb a, f where a.pid=f.id
)
select * from f