解决了很久都解决不了的一个sql问题，请大家帮忙

[yaoyi698]

有一张表有SN 和 ParentSN 这两个字段，用着张表的数据加载一个树形菜单，SN是每一个节点的编号，ParentSN是这个节点的父节点的SN

例如：
SN ParentSN
001 0
0011 001
0012 001
002 0
0021 002
0022 002


现在要查出所有最底层节点的信息，就是没有子节点的节点信息


我的思路：找出所有节点的SN，在找出所有节点的ParentSN，然后再找哪些SN不再ParentSN中，就是没有子节点的，sql如下：
select * from V1_GongZuoBiaoZhunJiBenBiao where SN not in (select distinct ParentSN from V1_GongzuoBiaoZhunJiBenBiao)


问题：
select * from V1_GongZuoBiaoZhunJiBenBiao 查出的结果有692条

select distinct ParentSN from V1_GongzuoBiaoZhunJiBenBiao 查出的结果有292条

但整个查出来就是0条，不知道为什么，感觉逻辑没有错，请高手指点

[SQL77]

BOM帮顶

[dawugui]

select m.* from tb m where not exists(select 1 from tb n where sn = m.ParentSN)

[pt1314917]

select * from V1_GongZuoBiaoZhunJiBenBiao  a
where not exists(select 1 from V1_GongZuoBiaoZhunJiBenBiao where parentsn=a.sn)

[dawugui]

[Quote=引用 2 楼 dawugui 的回复:]
select m.* from tb m where not exists(select 1 from tb n where sn = m.ParentSN)
[/Quote]
上面写反了,这个才对.

create table tb(SN varchar(10),     ParentSN varchar(10))
insert into tb values('001 '  ,  '0') 
insert into tb values('0011'  ,  '001') 
insert into tb values('0012'  ,  '001') 
insert into tb values('002 '  ,  '0') 
insert into tb values('0021'  ,  '002') 
insert into tb values('0022'  ,  '002')
go

select m.* from tb m where not exists(select 1 from tb n where ParentSN = m.sn) 
 
drop table tb

/*
SN         ParentSN   
---------- ---------- 
0011       001
0012       001
0021       002
0022       002

（所影响的行数为 4 行）
*/

[yaoyi698]

我的为什么不对呀

[bancxc]

感觉你的也是对的呀

[--小F--]

--这样?
select
 * 
from
 V1_GongZuoBiaoZhunJiBenBiao t
where
 not exists(select 1 from V1_GongZuoBiaoZhunJiBenBiao where parentsn=t.sn)

[xman_78tom]

declare @tb table(SN varchar(10),ParentSN varchar(10))
insert into @tb values('001 ','0') 
insert into @tb values('0011','001') 
insert into @tb values('0012','001') 
insert into @tb values('002 ','0') 
insert into @tb values('0021','002') 
insert into @tb values('0022','002')

select * from @tb where sn not in (select ParentSN from @tb)
/*
0011	001
0012	001
0021	002
0022	002
*/
-- ParentSN 有 null 值
declare @tb table(SN varchar(10),ParentSN varchar(10))
insert into @tb values('001 ',NULL) 
insert into @tb values('0011','001') 
insert into @tb values('0012','001') 
insert into @tb values('002 ','0') 
insert into @tb values('0021','002') 
insert into @tb values('0022','002')

select * from @tb where sn not in (select ParentSN from @tb)
/* */
select * from @tb t where not exists (select 1 from @tb where t.sn=ParentSN)
/*
0011	001
0012	001
0021	002
0022	002
*/