sql - oracle sql 按偏好搜索

Question

获取样本表:

WITH t(val) AS
     ( SELECT 'my Name' FROM dual
     UNION
     SELECT 'my name' FROM dual
     UNION
     SELECT 'my naim' FROM dual
     UNION
     SELECT 'MY kat' FROM dual
     UNION
     select 'my katt' from dual
    )
SELECT * FROM t;

我需要一个优先输出:

查询应该先在表中搜索完全匹配，如果没有找到则通过小写搜索，如果没有找到则只搜索soundex,。所以最终的输出是这样的:

WHERE val = 'my Name'
  OR lower(val) = lower('my Name')
  OR soundex(lower(val)) = soundex(lower('my Name'))

应该是:

output
-----
my Name

提前致谢。

Answer 1

按照您的描述进行过滤，然后按该顺序排序，然后获取第一条记录:

WITH t AS
     ( SELECT 'my Name' as val FROM dual
     UNION
     SELECT 'my name' FROM dual
     UNION
     SELECT 'my naim' FROM dual
     UNION
     SELECT 'MY kat' FROM dual
     UNION
     select 'my katt' from dual
    )
SELECT * FROM
(
  SELECT * FROM t
  WHERE val = 'my Name'
  OR lower(val) = lower('my Name')
  OR soundex(lower(val)) = soundex(lower('my Name'))
  order by
    case 
      when val = 'my Name' then 1
      when lower(val) = lower('my Name') then 2
      when soundex(lower(val)) = soundex(lower('my Name')) then 3
    end
)
WHERE ROWNUM = 1;