模糊查询LIKE按查询的where子句中的字段顺序对sql查询的结果进行排序

问题描述

我想根据查询的内容对查询结果进行排序:

SQL 模糊查询后按匹配度排序 CASE WHEN THEN

SQL学习（排序检索数据，过滤数据）

SELECT a from Frais a where a.libelle = 'FRET' or a.libelle = 'Douane' or a.libelle = 'Transitaire'

方案

SELECT a from Frais a where a.libelle = 'FRET' or a.libelle = 'Douane' or a.libelle = 'Transitaire'
order by case a.libelle when 'FRET' then 0 when 'Douane' then 1 when 'Transitaire' then 2 end

select * from acs.COMMODITY_INFO_MASTER 
where jan_code like '4536117005899%' or jan_code like '202203260000%' or jan_code like '4536117001860%'or jan_code like '4536117005899%'
order by case 
when jan_code like '4536117005899%' then 0 
when jan_code like '202203260000%' then 1 
when jan_code like '4536117005899%' then 2 
when jan_code like '4536117005899%' then 3 end
-- 排序前 4536117005899 4536117001860 2022032600001 2022032600002 2022032600003 2022032600004 2022032600005
-- 排序后 4536117005899 2022032600001 2022032600002 2022032600003 2022032600004 2022032600005 4536117001860

若我想在模糊查询后，进行排序，完全匹配的放在最前面，其余的放后面，可以使用case when then

如：

SELECT * FROM “table1” WHERE name LIKE ‘%玉米%’
ORDER BY (
CASE
WHEN name=’玉米’ THEN 1
WHEN name like ‘玉米%’ THEN 2
WHEN name like ‘%玉米%’ THEN 3
WHEN name like ‘%玉米’ THEN 4
ELSE 5
END
)

顺序是：完全匹配>开头匹配>中间匹配>最后匹配

else后面的数字代表剩余的情况则按ELSE后的数字顺序排，5>4,所以剩余的情况会排在最后

查询结果如图：