Select * From TEST A Where Not Exists(Select 1 From TEST Where A.ID Like ID +'%' And ID<>A.ID)
Create Table TEST (ID Varchar(20)) Insert TEST Select'1001001' Union All Select '1001' Union All Select '2001001' Union All Select '2001' Union All Select '300120013001' GO Select * From TEST A Where Not Exists(Select 1 From TEST Where A.ID Like ID +'%' And ID<>A.ID) GO Drop Table TEST GO --Result /* ID 1001 2001 300120013001 */
Select * From TEST A Where Not Exists(Select 1 From TEST Where A.ID Like ID +'%' And ID<>A.ID)
gaojier1000(青岛※高捷) ,Ctrl +C ,Ctrl + V練的蠻熟練啊。
/* 写好之后,发现和游鱼的方法一样. 因为有点补充, 所以也一并把它贴上来吧. */ if object_id('tt') is not null drop table tt go create table tt(ch varchar(20))insert tt select '1001001' union all select '1001' union all select '2001001' union all select '2001' union all select '300120013001' union all select '4001'--求森林的根 select * from tt as t1 where not exists (select 1 from tt as t2 where t1.ch like t2.ch+'%' and t1.ch!=t2.ch) --求森林的叶 select * from tt as t1 where not exists (select 1 from tt as t2 where t2.ch like t1.ch+'%' and t1.ch!=t2.ch)
(ID Varchar(20))
Insert TEST Select'1001001'
Union All Select '1001'
Union All Select '2001001'
Union All Select '2001'
Union All Select '300120013001'
GO
Select * From TEST A Where Not Exists(Select 1 From TEST Where A.ID Like ID +'%' And ID<>A.ID)
GO
Drop Table TEST
GO
--Result
/*
ID
1001
2001
300120013001
*/
写好之后,发现和游鱼的方法一样.
因为有点补充, 所以也一并把它贴上来吧.
*/
if object_id('tt') is not null drop table tt
go
create table tt(ch varchar(20))insert tt select '1001001'
union all select '1001'
union all select '2001001'
union all select '2001'
union all select '300120013001'
union all select '4001'--求森林的根
select *
from tt as t1
where not exists
(select 1
from tt as t2
where t1.ch like t2.ch+'%'
and t1.ch!=t2.ch)
--求森林的叶
select *
from tt as t1
where not exists
(select 1
from tt as t2
where t2.ch like t1.ch+'%'
and t1.ch!=t2.ch)
加分!