sql - Oracle 给予 like %term 的优先级高于 %term%

Question

我有一个类似这样的查询

select city_desc from mst_city where upper(city_desc) like upper('%branch%')

它填充结果以分支开头的结果和包含分支的结果。顺序随机。

我想对其进行排序，以便结果首先显示所有以branch开头的搜索结果，然后显示所有包含branch的搜索结果。

我该怎么做。我知道我可能必须使用 Order By 子句。但是我想不通

Answer 1

尝试以下解决方案:

WITH mst_city AS
  (SELECT 'branch test' AS city_desc FROM dual
   UNION ALL SELECT 'test branch test' FROM dual
   UNION ALL SELECT 'a branch' FROM dual
   UNION ALL SELECT 'branch' FROM dual
   UNION ALL SELECT 'something different' FROM dual)
SELECT
    city_desc
  FROM (
    SELECT
        city_desc,
        CASE
          WHEN UPPER(city_desc) LIKE 'BRANCH%' THEN 1
          ELSE 2
        END AS match_order
      FROM mst_city
    WHERE
      UPPER(city_desc) LIKE '%BRANCH%'
    )  
ORDER BY match_order;