如何在MySQL中按指定优先级列表获取首个匹配的所有行？

样本数据表 #

需求说明 #

需要根据有序优先级列表提取数据，规则如下：

• 优先获取列表中第一个值匹配Col 1的所有行；
• 若第一个值无匹配，则获取列表中下一个值匹配的所有行，以此类推。

示例1 #

当优先级列表为(Val 10, Val 1, Val 5, Val 6)时，因无Val 10的行，返回所有Col 1为Val 1的行，预期结果：

示例2 #

当优先级列表为(Val 6, Val 10, Val 1, Val 5)时，直接返回Col 1为Val 6的行，预期结果：

已尝试的SQL语句 #

SELECT `Col 2` 
FROM `table1` 
WHERE `Col 1` IN ('Val 10', 'Val 1', 'Val 5', 'Val 6')
ORDER BY FIELD(`Col 1`, 'Val 10', 'Val 1', 'Val 5', 'Val 6')

该语句会返回所有匹配列表中值的行，目前只能通过代码端的for循环实现需求，求更优的MySQL查询方案？

解决方案 #

方法1：子查询锁定最高优先级匹配值 #

先找到优先级列表中第一个存在于Col 1的值，再查询该值对应的所有行：

SELECT `Col 1`, `Col 2`
FROM `table1`
WHERE `Col 1` = (
    SELECT `Col 1`
    FROM `table1`
    WHERE `Col 1` IN ('Val 10', 'Val 1', 'Val 5', 'Val 6')
    ORDER BY FIELD(`Col 1`, 'Val 10', 'Val 1', 'Val 5', 'Val 6')
    LIMIT 1
);

原理：子查询通过FIELD()函数按指定优先级排序，取第一个存在的Col 1值，外层查询匹配该值返回所有对应行。

方法2：变量标记最高优先级 #

适合MySQL 5.x及以上版本，通过变量记录首个匹配的优先级值：

SELECT `Col 1`, `Col 2`
FROM (
    SELECT 
        `Col 1`, 
        `Col 2`,
        FIELD(`Col 1`, 'Val 10', 'Val 1', 'Val 5', 'Val 6') AS priority_rank,
        @top_priority := IF(@top_priority IS NULL, priority_rank, @top_priority)
    FROM `table1`, (SELECT @top_priority := NULL) AS init_var
    WHERE `Col 1` IN ('Val 10', 'Val 1', 'Val 5', 'Val 6')
    ORDER BY priority_rank
) AS ranked
WHERE priority_rank = @top_priority;

原理：先按优先级排序，用变量初始化并记录首个出现的优先级值，最后筛选出所有与该优先级值匹配的行。

注意事项 #

• 两种方法都需要保证FIELD()函数中的值顺序和优先级列表完全一致；
• 若优先级列表中的值均不存在于表中，查询会返回空结果，符合需求逻辑。

内容的提问来源于stack exchange，提问作者Jayarathina Madharasan