如何用MySQL将指定行置于结果集的特定位置？

我有一个数据表，原本使用以下SQL语句查询：

SELECT * FROM table ORDER BY rating DESC LIMIT 10;

表中存在position列，我希望当该列值为4时，对应行显示为结果集的第4条；值为7时显示为第7条，以此类推；其余行则按rating列降序排列。

我尝试使用CASE语句实现，代码如下：

SELECT * FROM table 
    ORDER BY 
    CASE WHEN position = 4 THEN 4
    WHEN position = 9 THEN 9 
    ELSE 0 END,
    rating DESC
LIMIT 10;

但该语句会隐藏position值为4和9的行。

原查询语句返回的结果表：

我期望得到的结果表：

请问是否可以实现该需求？若可以，正确的SQL语句是什么？

可以实现该需求。你之前的CASE语句逻辑存在问题：它将固定位置的行排序权重设为4、9，而非固定行权重设为0，导致固定行排在所有非固定行之后，再加上LIMIT 10的限制，这些固定行因原rating较低被挤出了结果集。

正确的思路是给每行分配动态调整的排序键，让固定位置行插入到指定位置，同时不打乱其余行的原排序顺序，具体SQL如下：

SELECT t.*
FROM (
    SELECT 
        *,
        -- 按rating降序生成基础排序序号
        ROW_NUMBER() OVER (ORDER BY rating DESC) AS base_rank,
        -- 标记固定位置行的目标位置，非固定行设为NULL
        CASE WHEN position IN (4, 9) THEN position ELSE NULL END AS fixed_pos
    FROM `table`
) t
ORDER BY 
    CASE 
        -- 固定位置行直接用目标位置作为排序键
        WHEN fixed_pos IS NOT NULL THEN fixed_pos
        -- 非固定行的排序键：基础序号加上排在它之前的固定位置行数量，跳过已占用的位置
        ELSE base_rank + (SELECT COUNT(*) FROM `table` WHERE position IN (4,9) AND position < base_rank)
    END
LIMIT 10;

逻辑说明 #

1. 子查询通过ROW_NUMBER()给所有行按rating降序生成基础序号base_rank，确定非固定行的原始排序位置。
2. 标记出需要固定位置的行的fixed_pos，非固定行设为NULL。
3. 排序阶段：
   • 固定位置行直接使用fixed_pos作为排序键，确保它们落在指定位置。
   • 非固定行在基础序号的基础上，加上"排在它之前的固定位置行数量"，以此跳过固定行已占用的位置，避免排序冲突。

这样就能得到你期望的结果：固定位置行插入到指定位置，其余行保持原rating降序的顺序，且不会遗漏任何行。

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