17,377
社区成员
发帖
与我相关
我的任务
分享
--用case...when...,满足1个条件计算为1,否则计算为0;最后按照满足条件的总和排序。
select id, title, dept, nationality from(
select test.*,
(case when title like '%aa%' then 1 else 0 end) title_weight,
(case when dept like '%priv%' then 1 else 0 end) dept_weight,
(case when nationality like '%China%' then 1 else 0 end) nationality_weight
from test
) where (title_weight+dept_weight+nationality_weight)>0
order by (title_weight+dept_weight+nationality_weight) desc;