id title dept nationality
1 aa gove China
2 bb priv france
3 cc gov Germany
4 aa priv Chinaselect * from test where title like '%aa%' or dept like '%priv%' or nationality like '%China%'如何排序得到4 aa priv China
1 aa gove China
2 bb priv france
1 aa gove China
2 bb priv france
3 cc gov Germany
4 aa priv Chinaselect * from test where title like '%aa%' or dept like '%priv%' or nationality like '%China%'如何排序得到4 aa priv China
1 aa gove China
2 bb priv france
select * from test where title like '%aa%' or dept like '%priv%' or nationality like '%China%' order by 2,3 desc;
数字代表你select字段的第一个,第二个...字段。
4 aa priv China 满足3个like
1 aa gove China 满足2个like
2 bb priv france 满足1个like
在test表中加个字段,PaiXu,每次查询后再根据查询条件把所有记录根据符合条件数写进PaiXu字段中
再排序?
但感觉太过复杂
--用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;