求一条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

上面是一个树型的数据结构，现想查询出每一课树的最高节点，树下面的分支都不显示望各位仁兄帮忙.
对于上面的数据想要的结果如下：
CompanyID CompanyName CompanyFatherID
-----------------------------------------
10001 AA 0

10006 FF 0

10010 KK 10015

10014 QQ 10016

[解决办法]
select * from tbl t
where CompanyID not in(select CompanyID from tbl where t.companyFatherID =CompanyID)
[解决办法]
的法

Select
A.*
From
TableName A
Left Join
TableName B
On A.CompanyFatherID = B.CompanyID

Where B.CompanyID Is Null
[解决办法]
Create Table Company
(CompanyIDVarchar(10),
CompanyNameVarchar(20),
CompanyFatherIDVarchar(10))
Insert Company 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 '
GO
Select
A.*
From
Company A
Left Join
Company B
On A.CompanyFatherID = B.CompanyID
Where B.CompanyID Is Null
Go
Drop Table Company
--Result
/*
CompanyIDCompanyNameCompanyFatherID
10001AA0
10006FF0
10010KK10015
10014QQ10016
*/
[解决办法]
借用鱼的列子:
declare @Company Table
(CompanyIDVarchar(10),
CompanyNameVarchar(20),
CompanyFatherIDVarchar(10))
Insert @Company 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 @Company
where CompanyFatherID!=all(select CompanyID from @Company)


(14 行受影响)
CompanyID CompanyName CompanyFatherID
---------- -------------------- ---------------
10001 AA 0
10006 FF 0
10010 KK 10015
10014 QQ 10016

(4 行受影响)


[解决办法]

Create Table Company1
(CompanyIDVarchar(10),
CompanyNameVarchar(20),
CompanyFatherIDVarchar(10))
Insert Company1 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 company1 a
where not exists(select 1 from company1 where a.companyfatherid=companyid )

CompanyID CompanyName CompanyFatherID
---------- -------------------- ---------------
10001 AA 0
10006 FF 0
10010 KK 10015
10014 QQ 10016

（所影响的行数为 4 行）