搜索查询，'order by' 优先级 [英] Search query, 'order by' priority

问题描述

我需要在小内容表中实现简单的搜索:id、名称、描述、内容.结果必须按优先级排序

I need implement simple search in small content table: id, name, description, content. Results must be order by priorities

1. 姓名
2. 说明
3. 内容

意思是如果在描述字段中找到搜索词，它只会显示在所有名称字段中包含狗的行之后

it's mean that if searched word was found in description field, it will be shown only after all rows that have dog in name field

我做了什么:

我尝试创建临时表，其结构类似于我使用的表，但具有另一个字段优先级.对于我用于搜索的每个字段，执行插入选择到临时表

I tried create temp table with structure like table that I use but with another field priority. for each field, these I use for search, do insert select to temp table

示例:

  DECLARE @query NVARCHAR(255)


  CREATE TABLE #tbl_search
    (
      [id] INT NOT NULL ,
      [name] NVARCHAR(255) ,
      [description] NVARCHAR(MAX) ,
      [content] NVARCHAR(MAX) ,
      [priority] INT
    )    


    --searching in name field
  INSERT    INTO #tbl_search
            ( [ID] ,
              [name] ,
              [description] ,
              [content] ,
              [priority]

            )
            SELECT  [ID] ,
                    [name] ,
                    [description] ,
                    [content] ,
                    1
            FROM    [tbl_content]
            WHERE   name LIKE '%' + @query + '%'

    --searching in description field            
  INSERT    INTO #tbl_search
            ( [ID] ,
              [name] ,
              [description] ,
              [content] ,
              [priority]

            )
            SELECT  [ID] ,
                    [name] ,
                    [description] ,
                    [content] ,
                    2
            FROM    [tbl_content]
            WHERE   description LIKE '%' + @query + '%'
                    AND id NOT IN ( SELECT  id
                                    FROM    #tbl_search )
   --.....           

  SELECT    *
  FROM      #tbl_search
  ORDER BY  [priority]

  DROP TABLE #tbl_search

推荐答案

一种方法是使用 CASE 关键字:

One way of doing it would be using the CASE keyword:

SELECT name, description, content,
    priority = CASE
        WHEN name LIKE '%' + @query + '%' THEN 1
        WHEN desription LIKE '%' + @query + '%'  THEN 2
        WHEN content LIKE '%' + @query + '%'  THEN 3
    END CASE
FROM tbl_content
WHERE
    name LIKE '%' + @query + '%' OR
    desription LIKE '%' + @query + '%'  OR
    content LIKE '%' + @query + '%'
ORDER BY priority ASC

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