我几乎肯定会选择银的解决方案

您的声明：

没有什么意义。将两个结果集拆分/切片/拼接在一起不太可能对性能产生可衡量的影响。它肯定不会使“请求变慢”！

这是针对您的场景的 SQL 解决方案，但几乎肯定会比将两个结果集拼接在一起要慢，具体取决于所涉及的表的大小。

-- this cte just gives is a contiguous sequence from 1 to number of toys WITH RECURSIVE seq (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < (SELECT COUNT(*) FROM toys) ) SELECT title, rank_index, created_at, n FROM ( -- we now add row_number to the seq after removing the seq numbers -- already used by rank_index SELECT seq.n, ROW_NUMBER() OVER (ORDER BY seq.n) AS rn FROM seq WHERE NOT EXISTS (SELECT 1 FROM toys WHERE rank_index = seq.n) ) x JOIN ( -- get toys without rank_index and add row_number for join to prev subquery SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC) rn FROM toys WHERE rank_index IS NULL ) y USING (rn) UNION ALL SELECT title, rank_index, created_at, rank_index FROM toys WHERE rank_index IS NOT NULL -- applies to the result of UNION ORDER BY n;

如果你有超过 1000 个玩具，递归 cte 将达到默认值cte_max_recursion_depth，如此处解释。

您可以在上述查询之前运行以下命令来删除限制：

SET SESSION cte_max_recursion_depth = 10000; -- permit 10,000 iterations SET SESSION cte_max_recursion_depth = 0; -- unlimited iterations

或更改递归 CTE到非递归 CTE，其中toys 表上的“nofollow noreferrer">ROW_NUMBER()：

WITH seq (n) AS ( SELECT ROW_NUMBER() OVER (ORDER BY id) FROM toys )

这是一个可以玩的dbfiddle。

我今年就遇到过，直接在查询中排序比较复杂，可以参考这个问题MySQL 结果集按固定位置排序如果你想深入了解它的复杂性。

我之前做的比较简单，通过两个查询完成，

• 首先是查询当前分页范围内的固定项目。
• 那么第二个查询是按日期排序的标准分页查询，
• 然后将固定项目推送到分页查询，并使用基于其列值的索引。

这是一个您可以参考的示例

$perPage = 10; $page = request('page') ?? 1; $start = ($page - 1) * $perPage + (1); // get the start number of the pagination $end = $perPage * $page; // get the end number of the pagination //query the pinned items with fixed position between start and end of the current pagination $pinned = Model::select('title','rank_index','created_at')->whereBetween('rank_index', [$start, $end])->get(); //standard pagination query, exclude the pinned items (if rank_index has value) //you can also subtract the pinned result count on pagination if you want i.e. ->paginate( $perPage - $pinned->count() ) //but I prefer to leave it and modify the limit on the collection as to not messed the pagination per_page value which could potentially messed-up the front-end $result = Model::select('title','rank_index','created_at')->whereNull('rank_index')->orderBy('created_at', 'DESC')->paginate( $perPage ); // insert the pinned items to the pagination data with index based on rank_index value $pinned->sortBy('rank_index')->each(function ($item) use (&$result) { $index = $item['rank_index'] - 1; $result->splice($index < 0 ? 0 : $index, 0, [$item]); }); //making sure to only take the total of perPage incase there is a pinned item inserted on the paginated data $result->setCollection($result->take($perPage)); return [ 'start' => $start, 'end' => $end, 'pinned' => $pinned, 'result' => $result ];