select * from TB_temp where len(FJZF) >= 3 and SUBSTRING(FJZF,1,1) = 'A' order by FJZF 我要分啊
大致写了一下,好像不用那么麻烦的,先放上来再说 create table tb(text varchar(20)) insert tb select 'a' union select 'a1' union select 'a2' union select 'a12' union select 'a131' select * from (select text from tb a where left(text,1)='a') b where not exists(select count(1) from (select text from tb a where left(text,1)='a') a where charindex(b.text,a.text)>0 having count(1)>1)
SUBSTRING(FJZF,1,1) = 'A'是说明FJZF字段的第一个字母是A
明白你的意思了 select * from TB_temp where ( (SUBSTRING(FJZF,1,2) = 'A1' and len(FJZF) >= 3 ) or (SUBSTRING(FJZF,1,2) <> 'A1' )) and SUBSTRING(FJZF,1,1) = 'A' order by FJZF 这个包含了A2,A3的记录
select distinct a.fjzf from tb_temp a left join ( select left(' '+fjzf ,len(fjzf)-1) as s from tb_temp ) b on ' '+a.fjzf =b.s where fjzf like 'A%' and b.s is null
多谢各位! victorycyz(中海)明白了我的意思 :) 不容易啊,高人! 不过更确切的结果应该是这样,怨我没有表达好啊 ^_^select distinct a.fjzf from tb_temp a left join ( select left(' '+fjzf ,len(fjzf)) as s from tb_temp ) b on ' '+a.fjzf =b.s where fjzf like 'A%' and b.s is null
select * from TB_temp
where len(FJZF) >= 3
and SUBSTRING(FJZF,1,1) = 'A'
order by FJZF
我要分啊
create table tb(text varchar(20))
insert tb select 'a' union select 'a1' union select 'a2' union select 'a12' union select 'a131'
select * from
(select text from tb a where left(text,1)='a') b
where not exists(select count(1) from
(select text from tb a where left(text,1)='a') a
where charindex(b.text,a.text)>0 having count(1)>1)
select * from TB_temp
where ( (SUBSTRING(FJZF,1,2) = 'A1' and len(FJZF) >= 3 ) or (SUBSTRING(FJZF,1,2) <> 'A1' ))
and SUBSTRING(FJZF,1,1) = 'A'
order by FJZF
这个包含了A2,A3的记录
select distinct a.fjzf
from tb_temp a left join
( select left(' '+fjzf ,len(fjzf)-1) as s
from tb_temp
) b
on ' '+a.fjzf =b.s
where fjzf like 'A%' and b.s is null
from tb_temp a left join
( select left(' '+fjzf ,len(fjzf)) as s
from tb_temp
) b
on ' '+a.fjzf =b.s
where fjzf like 'A%' and b.s is null