救命呀求一条sql语句！
求一条sql语句，望各位仁兄帮忙
CompanyID CompanyName CompanyFatherID
-----------------------------------------
10001 AA 0
10002 BB 10001
10003 CC 10002
10004 DD 10001
10005 EE 10003
10006 FF 0
10007 GG 10006
10008 HH 10006
10009 JJ 10007
10010 KK 10015
10011 LL 10010
10012 MM 10010
10013 NN 10011
10014 QQ 10016
上面是一个树型的数据结构，现想查询出每一课树的最高节点，树下面的分支都不显示望各位仁兄帮忙.
对于上面的数据想要的结果如下：（还有一下查询条件 可以按照CompanyName 和CompanyCode查询）
CompanyID CompanyName CompanyFatherID
-----------------------------------------
10001 AA 0
10006 FF 0
10010 KK 10015
10014 QQ 10016
以前我也问过这个问题，但是执行起来速度好慢呀！望各位仁兄帮助 !
[解决办法]
select CompanyID , CompanyName , CompanyFatherID
from table
where CompanyFatherID not in (select CompanyID from table)
[解决办法]
create table #t(CompanyID varchar(10),CompanyName varchar(10),CompanyFatherID varchar(10))
insert into #t
select '10001 ', 'AA ', '0 '
union all select '10002 ', 'BB ', '10001 '
union all select '10003 ', 'CC ', '10002 '
union all select '10004 ', 'DD ', '10001 '
union all select '10005 ', 'EE ', '10003 '
union all select '10006 ', 'FF ', '0 '
union all select '10007 ', 'GG ', '10006 '
union all select '10008 ', 'HH ', '10006 '
union all select '10009 ', 'JJ ', '10007 '
union all select '10010 ', 'KK ', '10015 '
union all select '10011 ', 'LL ', '10010 '
union all select '10012 ', 'MM ', '10010 '
union all select '10013 ', 'NN ', '10011 '
union all select '10014 ', 'QQ ', '10016 '
select * from #t
where not exists(select 1 from #t t where t.CompanyID=#t.CompanyFatherID)
/*
CompanyID CompanyName CompanyFatherID
---------- ----------- ---------------
10001 AA 0
10006 FF 0
10010 KK 10015
10014 QQ 10016
（所影响的行数为 4 行）
*/
drop table #t
[解决办法]
declare @t table(CompanyID varchar(10),CompanyName varchar(10),CompanyFatherID varchar(10))
insert into @t
select '10001 ', 'AA ', '0 '
union all select '10002 ', 'BB ', '10001 '
union all select '10003 ', 'CC ', '10002 '
union all select '10004 ', 'DD ', '10001 '
union all select '10005 ', 'EE ', '10003 '
union all select '10006 ', 'FF ', '0 '
union all select '10007 ', 'GG ', '10006 '
union all select '10008 ', 'HH ', '10006 '
union all select '10009 ', 'JJ ', '10007 '
union all select '10010 ', 'KK ', '10015 '
union all select '10011 ', 'LL ', '10010 '
union all select '10012 ', 'MM ', '10010 '
union all select '10013 ', 'NN ', '10011 '
union all select '10014 ', 'QQ ', '10016 '
select CompanyID,CompanyName,CompanyFatherID from @t a
where not exists(select 1 from @t where CompanyID=a.CompanyFatherID)
[解决办法]
select * from table1
where companyfatherid not in(select companyid from table1)
[解决办法]
換個companyid 位置：
select * from table1
where companyid not in(select companyfatherid from table1)
[解决办法]
自联接是不是速度特别慢呀！马上快崩溃了！谢谢上面的兄弟们
=========>
要具体情况具体分析,自联接和其它联接是一样的.
