如何按INT类型列总分排序？两类MySQL查询需求

Q1：按总分降序排序的查询 #

需求实现 #

• 筛选出包含mobile、phone、tutorial、apps中任意1-4个关键词的行
• 统计每行所有point列的总分
• 按总分降序排列结果

示例SQL #

SELECT 
    id,
    kw1, kw1_point,
    kw2, kw2_point,
    kw3, kw3_point,
    kw4, kw4_point,
    (kw1_point + kw2_point + kw3_point + kw4_point) AS total_score
FROM keywords
WHERE 
    kw1 IN ('mobile', 'phone', 'tutorial', 'apps')
    OR kw2 IN ('mobile', 'phone', 'tutorial', 'apps')
    OR kw3 IN ('mobile', 'phone', 'tutorial', 'apps')
    OR kw4 IN ('mobile', 'phone', 'tutorial', 'apps')
ORDER BY total_score DESC
LIMIT 100;

说明 #

• WHERE子句确保只返回至少包含一个目标关键词的行
• 通过kw1_point + kw2_point + kw3_point + kw4_point计算每行的总分，命名为total_score
• 最终按total_score降序排序，对应示例中的返回顺序：id3(20) → id2(11) → id0(10) → id1(8)

Q2：优先按匹配关键词数量排序的查询 #

需求实现 #

• 同Q1的筛选与总分统计规则
• 优先按匹配关键词数量降序排列（匹配数越少排名越靠后），匹配数相同时再按总分降序排列

示例SQL #

SELECT 
    id,
    kw1, kw1_point,
    kw2, kw2_point,
    kw3, kw3_point,
    kw4, kw4_point,
    (kw1_point + kw2_point + kw3_point + kw4_point) AS total_score,
    (
        IF(kw1 IN ('mobile', 'phone', 'tutorial', 'apps'), 1, 0) +
        IF(kw2 IN ('mobile', 'phone', 'tutorial', 'apps'), 1, 0) +
        IF(kw3 IN ('mobile', 'phone', 'tutorial', 'apps'), 1, 0) +
        IF(kw4 IN ('mobile', 'phone', 'tutorial', 'apps'), 1, 0)
    ) AS match_count
FROM keywords
WHERE 
    kw1 IN ('mobile', 'phone', 'tutorial', 'apps')
    OR kw2 IN ('mobile', 'phone', 'tutorial', 'apps')
    OR kw3 IN ('mobile', 'phone', 'tutorial', 'apps')
    OR kw4 IN ('mobile', 'phone', 'tutorial', 'apps')
ORDER BY match_count DESC, total_score DESC
LIMIT 100;

说明 #

• 新增match_count字段，通过IF函数统计每行匹配的目标关键词数量（匹配则加1，不匹配则加0）
• 排序时先按match_count降序，确保匹配数多的行优先；匹配数相同时，再按total_score降序排列，满足示例中id3（3个匹配词）排在id0、id1、id2（4个匹配词）之后的要求

内容的提问来源于stack exchange，提问作者Urgent Questioner