我幾乎肯定會選擇銀的解

您的聲明：

沒有什麼意義。將兩個結果集拆分/切片/拼接在一起不太可能對效能產生可衡量的影響。它肯定不會使“請求變慢”！

這是針對您的場景的 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 ];