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