代码之家  ›  专栏  ›  技术社区  ›  Luke Vo

T-SQL联合(distinct,而不是all)和order by with priority

  •  1
  • Luke Vo  · 技术社区  · 6 年前

    假设我有一个 Tag 表(表) Id int Name nvarchar(100) )有3个值:

    1 Software
    2 Hardware
    3 Warehouse
    

    现在我想用关键字进行查询,并将以关键字开头的查询优先于包含关键字的查询。因此,首先我编写这个查询:

    SELECT 0 AS SortCol, * 
    FROM Tag T
    WHERE CHARINDEX(@keyword, T.Name) = 1
    UNION
    SELECT 1 AS SortCol, * 
    FROM Tag T
    WHERE T.Name LIKE ('%' + @keyword + '%')
    ORDER BY SortCol, Name;
    

    但是,这不起作用,因为 SortCol 列不再区分它们(列 Warehouse 值出现两次,因为两者都正确)。

    enter image description here

    那时候我想我需要手动打电话 DISTINCT 把那一栏去掉:

    SELECT DISTINCT T2.Id, T2.Name
    FROM
        (SELECT 0 AS SortCol, * 
         FROM Tag T
         WHERE CHARINDEX(@keyword, T.Name) = 1
         UNION
         SELECT 1 AS SortCol, * 
         FROM Tag T
         WHERE T.Name LIKE ('%' + @keyword + '%')
         ORDER BY SortCol, T.Name) AS T2;
    

    但是,这不起作用,因为我得到这个错误:

    ORDER BY子句在视图、内联函数、派生表、子查询和公用表表达式中无效,除非还指定了TOP、OFFSET或FOR XML。

    我这里缺什么?我如何使用 UNION 具有 ORDER BY 在这两个陈述中?

    3 回复  |  直到 6 年前
        1
  •  5
  •   Martin Smith    6 年前

    你不需要 UNION 你可以使用

    SELECT *
    FROM   Tag T
    WHERE  T.Name LIKE '%' + @keyword + '%'
    ORDER  BY CASE WHEN T.Name LIKE @keyword + '%' THEN 0 ELSE 1 END,
              Name; 
    
        2
  •  1
  •   Lukasz Szozda    6 年前

    如果出于某种原因你需要这个 SortCol 列有可能:

    DECLARE @keyword NVARCHAR(MAX) = N'ware';
    
    WITH cte AS (
      SELECT *, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Name) rn
      FROM (
        SELECT 0 AS SortCol, * 
        FROM Tag T
        WHERE CHARINDEX(@keyword, T.Name) = 1
        UNION
        SELECT 1 AS SortCol, * 
        FROM Tag T
        WHERE T.Name LIKE ('%' + @keyword + '%')) s
    )
    SELECT SortCol, Id, name
    FROM cte
    WHERE rn = 1
    ORDER BY SortCol, Name;
    

    db<>fiddle demo

        3
  •  0
  •   GMB    6 年前

    直接在ORDERBY子句中放入一个案例,比如:

    SELECT
        *
    FROM Tag
    WHERE name like ( '%' + @keyword + '%' ) 
    ORDER BY
        CASE 
            WHEN CHARINDEX(@keyword, T.Name) = 1 THEN 0
            ELSE 1
        END,
        names