基于优先级队列搜索 [英] Search based on Priority queue

问题描述

我需要一些帮助..我正在尝试实施搜索结果



条件



1＃它应该获取的记录等于用户在文本框中输入的内容（@SEARCH）



2＃连同第一个条件它还会获取输入的匹配记录在文本框上（@SEARCH）



3＃搜索到的哪个关键字应显示第一个和其他匹配将在此之后显示（我的意思是优先级队列）



4＃应检查@loc paramater并获取该关键字。

 < span class =code-sdkkeyword> @ SEARCH   NVARCHAR （ 100 ）=  NULL ，
  @ loc   NVARCHAR （  100 ）=  NULL  
  as  
 选择 * 
 来自 tblBusinessCategory   b 
 < span class =code-keyword> inner   join  tblUser  as  u 上的code-keyword> b.BusinessID = u.BusinessCategoryId 
  inner   join  tblAddress  as  a  on  u.AddressId = a.AddressID 
  where  b.BusinessName =  @ SEARCH  和 
 a .City =  @ loc  
  OR  a.State =  @loc  
 和 b.BusinessName  LIKE  ' ％' +  @ SEARCH  + ' ％' 

解决方案

如果我理解你的话......

替换它：

 其中 b.BusinessName =  @ SEARCH  和 
 a.City =  @ loc  
  OR  a.State =  @ loc  
 和 b.BusinessName  LIKE  ' ％' +  @SEARCH  + ' ％' 

with：

 其中（b.BusinessName  LIKE  ' ％' +  @ SEARCH  + < span class =code-string>' ％' 和（a.City =  @ loc   OR  a.State =  @ loc ））

除非您想在满足其中一个条件时获取数据：

 其中（（b.BusinessName =  @ SEARCH  和 a.City =  @ loc ）
 或（a .State =  @ loc  和 b.BusinessName  LIKE  ' ％' +  @ SEARCH  + ' ％'））

关于OP的评论...我建议创建动态查询。请参阅：

在存储中构建动态SQL程序 [ ^ ]

使用动态查询 [ ^ ]





假设您要显示数据按升序排列，你必须添加 ORDER BY 子句：

  SELECT  * 
  FROM  TableName 
  WHERE  < ;条件>  
  ORDER   BY   FieldName  

 选择 * 
 来自 tblBusinessCategory  as  b 
  inner   join  tblUser  as  u  on  b.BusinessID = u.BusinessCategoryId 
  inner   join  tblAddress  as  a  on  u.AddressId = a.AddressID 
 其中​​ a.City =  @ loc  或 a.State =  @ loc  
  OR  b.BusinessName  LIKE  ' ％' +  @ SEARCH  + ' ％' 
 订单 < span class =code-keyword>按  case  何时 b.BusinessName =  @ SEARCH  然后  0   else   1   end  

这就是我需要的..

i need some help here.. am trying to implement a search result

conditions

1# it should fetch records that equals what keyword user enter on textbox(@SEARCH)

2# Along with the first condition It also fetch the matched records which is entered on textbox(@SEARCH)

3# What keyword searched that should show 1st and other matches will show after that( what i means priority queue)

4# It should check @loc paramater and fetch to that keyword.

@SEARCH NVARCHAR(100) = NULL,
@loc NVARCHAR(100) = NULL
as
select *
from tblBusinessCategory as b
    inner join tblUser as u on b.BusinessID=u.BusinessCategoryId
    inner join tblAddress as a on u.AddressId=a.AddressID
where b.BusinessName = @SEARCH and
a.City = @loc
       OR a.State = @loc
       and  b.BusinessName LIKE '%' + @SEARCH + '%'

解决方案

If i understand you well...
Replace this:

where b.BusinessName = @SEARCH and
a.City = @loc
       OR a.State = @loc
       and  b.BusinessName LIKE '%' + @SEARCH + '%'

with:

where (b.BusinessName LIKE '%' + @SEARCH + '%' and (a.City = @loc OR a.State = @loc))

unless you want to fetch data when one of possible conditions is meet:

where ((b.BusinessName = @SEARCH and a.City = @loc)
       OR (a.State = @loc and  b.BusinessName LIKE '%' + @SEARCH + '%'))

[EDIT]
As to the OP's comments... i would suggest to create dynamic queries. See:
Building Dynamic SQL In a Stored Procedure[^]
Using Dynamic Queries[^]

[EDIT 2]
Assuming that you want to display data in ascending order, you have to add ORDER BY clause:

SELECT *
FROM TableName
WHERE <condition>
ORDER BY FieldName

select *
from tblBusinessCategory as b
    inner join tblUser as u on b.BusinessID=u.BusinessCategoryId
    inner join tblAddress as a on u.AddressId=a.AddressID
where a.City = @loc OR a.State = @loc
       OR b.BusinessName LIKE '%' + @SEARCH + '%'
Order By case when b.BusinessName = @SEARCH then 0 else 1 end

this is what i need..

这篇关于基于优先级队列搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！