用于自动压缩的mssql查询

  • Z时代
  • 2024-01-10
  • 分类：问答

当我提问某个单词时，它会完成单词或向其添加新单词。 独一无二的前10个录音。用于自动压缩的mssql查询

declare @Category table (CategoryName varchar(50)) 
insert into @Category (CategoryName) values('phones') 
insert into @Category (CategoryName) values('phone blue') 
insert into @Category (CategoryName) values('phone pink') 
insert into @Category (CategoryName) values('phone big') 
insert into @Category (CategoryName) values('phone big bang') 
insert into @Category (CategoryName) values('bad phone blue') 
insert into @Category (CategoryName) values('bad phone pink') 
insert into @Category (CategoryName) values('bad phone but good')

的类别名称 “手机”

  • 电话蓝色

  • 手机粉色

  • 手机大

  • 手机，但

打电话时，我想下面的结果调用类别时，我希望获得以下结果名为 “PHON”

  • 电话

  • 手机

回答：

declare @Category table (CategoryName varchar(50)) 
insert into @Category (CategoryName) values('phone blue') 
insert into @Category (CategoryName) values('phone pink') 
insert into @Category (CategoryName) values('phone big') 
insert into @Category (CategoryName) values('phone big bang') 
insert into @Category (CategoryName) values('bad phone blue') 
insert into @Category (CategoryName) values('bad phone pink') 
insert into @Category (CategoryName) values('bad phone but good') 
insert into @Category (CategoryName) values('phone') 
insert into @Category (CategoryName) values('silly phone') 
insert into @Category (CategoryName) values('lost phone ') 
insert into @Category (CategoryName) values('white phone user') 
insert into @Category (CategoryName) values('some device') 
insert into @Category (CategoryName) values('phoner homer') 
insert into @Category (CategoryName) values('space phone ') 
insert into @Category (CategoryName) values('jam phone hex doc') 
declare @Formword varchar(50) = 'phone'; 
select CategoryName 
     --, charindex(@FormWord,Categoryname) 
     --, charindex(@FormWord,Categoryname) + len(@FormWord)-1 
     --, charindex(' ',CategoryName,charindex(@FormWord,Categoryname) + len(@FormWord)) 
     --, ltrim(right(categoryname,(len(categoryname)-charindex(' ',CategoryName,charindex(@FormWord,Categoryname) + len(@FormWord))))) 
     --, charindex(' ',ltrim(right(categoryname,(len(categoryname)-charindex(' ',CategoryName,charindex(@FormWord,Categoryname) + len(@FormWord)))))) 
     --, left(ltrim(right(categoryname,(len(categoryname)-charindex(' ',CategoryName,charindex(@FormWord,Categoryname) + len(@FormWord))))),charindex(' ',ltrim(right(categoryname,(len(categoryname)-charindex(' ',CategoryName,charindex(@FormWord,Categoryname) + len(@FormWord))))))) 
     , case when charindex(' ',ltrim(right(categoryname,(len(categoryname)-charindex(' ',CategoryName,charindex(@FormWord,Categoryname) + len(@FormWord)))))) = 0 
        then @Formword + ' ' + ltrim(right(categoryname,(len(categoryname)-charindex(' ',CategoryName,charindex(@FormWord,Categoryname) + len(@FormWord))))) 
       else 
        @Formword + ' ' + left(ltrim(right(categoryname,(len(categoryname)-charindex(' ',CategoryName,charindex(@FormWord,Categoryname) + len(@FormWord))))),charindex(' ',ltrim(right(categoryname,(len(categoryname)-charindex(' ',CategoryName,charindex(@FormWord,Categoryname) + len(@FormWord))))))) 
      end [next word] 
from @Category 
where 1 = 1 
     and CategoryName like '%' + @Formword + '%' 
     and len(CategoryName) > len(@Formword) + 1 
     and charindex(@FormWord,Categoryname) + len(@FormWord) < len(Categoryname)

回答：

declare @Category table (CategoryName varchar(50)) 
insert into @Category (CategoryName) values('phones') 
insert into @Category (CategoryName) values('phone blue') 
insert into @Category (CategoryName) values('phone pink') 
insert into @Category (CategoryName) values('phone big') 
insert into @Category (CategoryName) values('phone big bang') 
insert into @Category (CategoryName) values('bad phone blue') 
insert into @Category (CategoryName) values('bad phone pink') 
insert into @Category (CategoryName) values('bad phone but good') 
declare @searchWord varchar(50) = 'phone'; 
declare @res int; 
set @res = (select case when (select count(*) from @Category 
       where substring(CategoryName, 0, CHARINDEX(' ', CategoryName)) = @searchWord) > 0 
       then 1 
       else 0 
      end) 
if(@res = 1) 
begin 
    select top 10 CategoryName from @Category where CategoryName like''[email protected]+'%' and CategoryName like '% %' order by CategoryName 
end 
else 
begin 
    select top 10 CategoryName from @Category where CategoryName like''[email protected]+'%' and CategoryName not like '% %' order by CategoryName 
end

我希望这有助于。

以上是 用于自动压缩的mssql查询 的全部内容， 来源链接： utcz.com/qa/258095.html

回到顶部