如何按匹配条件数量排序SQL查询结果？优先返回匹配项最多的记录

要让匹配条件最多的记录排在最前面，核心思路就是给每条记录计算它满足了几个查询条件，然后按照这个计数从高到低排序。

这里给你两种实现方式，都能达到预期效果：

方法一：通用SQL写法（兼容大多数数据库） #

这种写法用CASE语句判断每个条件是否成立，成立则计1分，否则计0分，最后把分数相加得到匹配总数：

SELECT 
    *,
    -- 计算当前记录匹配的条件数量
    (CASE WHEN first_name ILIKE '%foo%' THEN 1 ELSE 0 END) +
    (CASE WHEN last_name ILIKE '%bar%' THEN 1 ELSE 0 END) +
    (CASE WHEN nickname ILIKE '%foobar%' THEN 1 ELSE 0 END) AS match_count
FROM users 
WHERE first_name ILIKE '%foo%' OR last_name ILIKE '%bar%' OR nickname ILIKE '%foobar%'
ORDER BY match_count DESC, first_name, last_name;

解释： #

1. match_count字段会返回每条记录匹配的条件个数（比如同时匹配first_name和last_name的记录，这个值就是2）
2. ORDER BY match_count DESC确保匹配越多的记录越靠前
3. 后面的first_name, last_name是可选的，用来保证当两条记录匹配数量相同时，排序结果稳定可预测

方法二：简化写法（适合PostgreSQL等支持布尔转整数的数据库） #

如果你的数据库支持将布尔值直接转为整数（TRUE=1，FALSE=0），可以用更简洁的写法：

SELECT 
    *,
    (first_name ILIKE '%foo%')::INT +
    (last_name ILIKE '%bar%')::INT +
    (nickname ILIKE '%foobar%')::INT AS match_count
FROM users 
WHERE first_name ILIKE '%foo%' OR last_name ILIKE '%bar%' OR nickname ILIKE '%foobar%'
ORDER BY match_count DESC;

对应你的示例数据，排序后的结果会是： #

这样就完美实现了“匹配条件越多越靠前”的需求。

内容的提问来源于stack exchange，提问作者Moamen Naanou