问题描述
我想根据查询的内容对查询结果进行排序:
SQL 模糊查询后按匹配度排序 CASE WHEN THEN
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,所以剩余的情况会排在最后
查询结果如图: