同意gsh945的思路,拿楼主的abnormal来说 declare @word varchar(25) declare @i tinyintset @word = 'abnormal' set @i=1 开始循环 if exists (select word_prefix from table_prefix where word_prefix = left(@word,@i)) print left(@word,@i) set @i = @i + 1 结束循环
如果必须要在一句查询里实现,用case试试呢
declare @a table(table_prefix varchar(10))insert @a select 'a' union all select 'ab' union all select 'abbr' select * from @a where 'abnormal' like (table_prefix +'%')
--测试数据插入 declare @a table(table_prefix varchar(10))insert @a select 'a' union all select 'ab' union all select 'abbr' --实际操作 select * from @a where 'abnormal' like (table_prefix +'%') --结果 (3 row(s) affected)table_prefix ------------ a ab(2 row(s) affected)
看看这个:(参考viptiger(六嘎)的) create table aaa (f1 varchar(10)) select * from aaa insert into aaa values('a') insert into aaa values('ab') insert into aaa values('abbr') select top 1 * from aaa where 'abnormal' like (f1 +'%') order by f1 desc
Create Table Table_prefix(word_prefix varchar(10), prefix_desc varchar(50)) insert into Table_prefix select 'a', '以a否定前' union select 'ab', '以ab的' union select 'abbr', '以abbr的' --------------實現語句----------------------- select * from Table_prefix where word_prefix= (select max(word_prefix) from Table_prefix where charindex(word_prefix,'abnormal')>0) -----------輸出----------------------------------ab 以ab的
select top 1 * from @a where 'abnormal' like (table_prefix +'%') order by table_prefix desc --结果 table_prefix ------------ ab
----或者----------- --------------實現語句----------------------- select top 1* from Table_prefix where charindex(word_prefix,'abnormal')>0 order by len(word_prefix) desc -----------輸出----------------------------------ab 以ab的
select top 1 * from table_prefix where 'abnormal' like (word_prefix +'%') order by word_prefix desc
declare @a table(table_prefix varchar(10)) --测试 insert @a select 'a' union all select 'ab' union all select 'abbr' --修改Softlee81307(孔腎) select max(table_prefix) from @a where charindex(table_prefix,'abnormal')>0
1、定义一个变量@tmp,使@tmp=left(word,1)
2、取出word的左边两位left(word,2),查找表table_prefix,如果找到,将@tmp=left(word,2),否则不赋值
3、取出word的左边3位,查找表table_prefix,如果找到,将@tmp=left(word,3),否则不赋值
....
....
4、直到len(word)=取值次数
最后,变量@tmp中存储的就是你想要的 word_prefix
由于前缀长度不定没法用like pattern这种查询条件~
呵呵,多谢!我开始也是这么想的,总觉得效率不太高~
不知道还有没有别的方法。
declare @word varchar(25)
declare @i tinyintset @word = 'abnormal'
set @i=1
开始循环
if exists (select word_prefix from table_prefix where word_prefix = left(@word,@i))
print left(@word,@i)
set @i = @i + 1
结束循环
select 'a'
union all
select 'ab'
union all
select 'abbr'
select * from @a
where 'abnormal' like (table_prefix +'%')
declare @a table(table_prefix varchar(10))insert @a
select 'a'
union all
select 'ab'
union all
select 'abbr' --实际操作
select * from @a
where 'abnormal' like (table_prefix +'%')
--结果
(3 row(s) affected)table_prefix
------------
a
ab(2 row(s) affected)
create table aaa (f1 varchar(10))
select * from aaa
insert into aaa values('a')
insert into aaa values('ab')
insert into aaa values('abbr')
select top 1 * from aaa where 'abnormal' like (f1 +'%') order by f1 desc
insert into Table_prefix
select 'a', '以a否定前' union
select 'ab', '以ab的' union
select 'abbr', '以abbr的'
--------------實現語句-----------------------
select * from Table_prefix where word_prefix=
(select max(word_prefix) from Table_prefix where charindex(word_prefix,'abnormal')>0)
-----------輸出----------------------------------ab 以ab的
where 'abnormal' like (table_prefix +'%')
order by table_prefix desc
--结果
table_prefix
------------
ab
--------------實現語句-----------------------
select top 1* from Table_prefix where charindex(word_prefix,'abnormal')>0 order by len(word_prefix) desc
-----------輸出----------------------------------ab 以ab的
--测试
insert @a
select 'a'
union all
select 'ab'
union all
select 'abbr' --修改Softlee81307(孔腎)
select max(table_prefix) from @a where charindex(table_prefix,'abnormal')>0