判断某记录树的表有无子节点

[p2227]

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',1

select * from @A

用什么语句可以查找出这样一个结果
id name haschild
1 r 1
2 r 0
3 c 0

[p2227]

只需要判断有无子节点就可以了，有多少个不需要判断

[Vidor]

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',1

select *, case when exists(select 1 from @A where pid=t.id) then 1 else 0 end from @A t

[dawugui]

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',1

select * , 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 行）
*/

[--小F--]

;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