表tb1有列field1,有如下数据:
field1
101-123-5
101-123
101-200
101-200-1-100
101-200-2
101-300
101-300-1
101-400-1-100-B我想取出如下数据:
field1
101-123
101-200
101-300
101-400-1-100-B
即,相同代码中取最短的一个请问有什么好的方法吗?想了好久了,谢谢各位朋友!
field1
101-123-5
101-123
101-200
101-200-1-100
101-200-2
101-300
101-300-1
101-400-1-100-B我想取出如下数据:
field1
101-123
101-200
101-300
101-400-1-100-B
即,相同代码中取最短的一个请问有什么好的方法吗?想了好久了,谢谢各位朋友!
insert into @t select '101-123-5'
insert into @t select '101-123'
insert into @t select '101-200'
insert into @t select '101-200-1-100'
insert into @t select '101-200-2'
insert into @t select '101-300'
insert into @t select '101-300-1'
insert into @t select '101-400-1-100-B'select
t.*
from
@t t
where
not exists(select 1 from @t where t.code like code+'%' and len(code)<len(t.code))
insert into @t select '101-123-5'
insert into @t select '101-123'
insert into @t select '101-200'
insert into @t select '101-200-1-100'
insert into @t select '101-200-2'
insert into @t select '101-300'
insert into @t select '101-300-1'
insert into @t select '101-400-1-100-B'select
t.*
from
@t t
where
not exists(select 1 from @t where t.code like code+'%' and len(code)<len(t.code))/*
code
------------------------------
101-123
101-200
101-300
101-400-1-100-B
*/
Where Not Exists (Select * From tb1 Where A.field1 Like '%' + field1 + '%' And A.field1 != field1)
(field1 Varchar(100))
Insert tb1 Select '101-123-5'
Union All Select '101-123'
Union All Select '101-200'
Union All Select '101-200-1-100'
Union All Select '101-200-2'
Union All Select '101-300'
Union All Select '101-300-1'
Union All Select '101-400-1-100-B'
GO
Select * From tb1 A
Where Not Exists (Select * From tb1 Where A.field1 Like '%' + field1 + '%' And A.field1 != field1)Select * From tb1 A
Where Not Exists (Select * From tb1 Where CharIndex(field1, A.field1) > 0 And A.field1 != field1)
GO
Drop Table tb1
--Result
/*
field1
101-123
101-200
101-300
101-400-1-100-B
*/
insert into @t select '101-123-5'
insert into @t select '101-123'
insert into @t select '101-200'
insert into @t select '101-200-1-100'
insert into @t select '101-200-2'
insert into @t select '101-300'
insert into @t select '101-300-1'
insert into @t select '101-400-1-100-B'select
t.*
from
@t t
where
not exists(select 1 from @t where t.code like code+'%' and code<t.code)