前言

日常開發(fā)經(jīng)常碰到兩類排序需求：

1. 跨表排序：依據(jù)一張表的查詢字段，控制另外一張業(yè)務(wù)表結(jié)果集排序；
2. 固定置頂：指定某一類數(shù)據(jù)強(qiáng)制排在列表最前面，剩余數(shù)據(jù)按原有規(guī)則排序。

很多新手只會單獨(dú)單表排序，遇到關(guān)聯(lián) + 置頂組合需求就無從下手，本文結(jié)合實(shí)戰(zhàn)場景，由淺入深梳理落地方案。

一、場景鋪墊

兩張業(yè)務(wù)表：

• sort_config：排序配置表，存儲商品自定義權(quán)重，goods_id關(guān)聯(lián)商品主鍵、weight排序權(quán)重；
• goods：商品主表，id主鍵、type商品類型、name商品名稱、price售價(jià)。

需求：根據(jù)sort_config篩選有效配置的權(quán)重排序商品，同時type=1（熱門商品）強(qiáng)制置頂。

二、需求 1：A 表?xiàng)l件驅(qū)動 B 表排序（JOIN 關(guān)聯(lián)排序）

核心思路：兩表關(guān)聯(lián)后，ORDER BY使用關(guān)聯(lián)出來的 A 表字段完成排序。

基礎(chǔ) SQL

SELECT g.*
FROM goods g
INNER JOIN sort_config sc ON g.id = sc.goods_id
WHERE sc.status = 1 -- A表篩選條件
ORDER BY sc.weight DESC; -- 使用A表權(quán)重給B表排序

要點(diǎn)說明

1. INNER JOIN：只帶出有配置權(quán)重的商品；LEFT JOIN 需要自行處理無權(quán)重?cái)?shù)據(jù)默認(rèn)排序；
2. WHERE 篩選 A 表數(shù)據(jù)，篩選后再依托 A 表字段完成整體排序；
3. 適用：配置表動態(tài)維護(hù)排序權(quán)重，前端不用傳排序字段，由數(shù)據(jù)庫配置控制。

三、需求 2：指定類型置頂 4 種常用方案

方案 1：FIELD 函數(shù)（推薦 MySQL5.7+，多值固定順序）

FIELD(字段,置頂值)配合DESC實(shí)現(xiàn)置頂，多類型自定義排序順序。

SELECT g.*
FROM goods g
JOIN sort_config sc ON g.id = sc.goods_id
WHERE sc.status = 1
ORDER BY
    FIELD(g.type,1) DESC, -- type=1置頂
    sc.weight DESC; -- 剩余按配置權(quán)重排序

多類型固定順序（1>3>2）寫法：

ORDER BY FIELD(g.type,1,3,2),sc.weight DESC

方案 2：布爾表達(dá)式置頂（極簡寫法）

利用 MySQL 布爾1/0特性，條件成立 = 1，倒序置頂。

ORDER BY g.type=1 DESC,sc.weight DESC

多類型置頂：

ORDER BY g.type IN(1,3) DESC,sc.weight DESC

方案 3：CASE WHEN（兼容低版本 MySQL，通用性最強(qiáng)）

適配所有 MySQL 版本，自定義排序分值，置頂數(shù)據(jù)分值設(shè) 0，其余設(shè)大值。

SELECT g.*
FROM goods g
JOIN sort_config sc ON g.id = sc.goods_id
WHERE sc.status = 1
ORDER BY
    CASE WHEN g.type =1 THEN 0 ELSE 1 END ASC,
    sc.weight DESC;

方案 4：數(shù)據(jù)庫冗余排序字段（大數(shù)據(jù)量最優(yōu)）

數(shù)據(jù)量大、千萬級分頁場景，不推薦函數(shù)排序，新增top_sort int字段，置頂數(shù)據(jù)存 0，普通數(shù)據(jù)存 9999。

ALTER TABLE goods ADD top_sort INT DEFAULT 9999;
-- type=1數(shù)據(jù)更新為0
UPDATE goods SET top_sort=0 WHERE type=1;

-- 查詢SQL（可命中索引）
SELECT g.*
FROM goods g
JOIN sort_config sc ON g.id = sc.goods_id
WHERE sc.status = 1
ORDER BY g.top_sort ASC,sc.weight DESC;

優(yōu)點(diǎn)：字段可建索引，分頁查詢性能遠(yuǎn)高于函數(shù)排序，海量數(shù)據(jù)首選。

四、四種方案選型總結(jié)

五、拓展優(yōu)化：開發(fā)避坑

1. 函數(shù)排序無法走索引：列表分頁量大時，盡量使用冗余字段方案；
2. LEFT JOIN 空值處理：左連接無配置權(quán)重?cái)?shù)據(jù)，可用IFNULL(sc.weight,0)兜底默認(rèn)權(quán)重；
3. 動態(tài)排序：若排序字段由配置表動態(tài)返回字段名，Java 后端拼接 SQL，不要在 SQL 內(nèi)動態(tài)解析字段。

結(jié)語

跨表排序 + 置頂是后臺列表通用需求，優(yōu)先小數(shù)據(jù)用FIELD快速實(shí)現(xiàn)，大數(shù)據(jù)提前設(shè)計(jì)排序冗余字段，從 SQL 層面提前規(guī)避后期分頁慢問題。

到此這篇關(guān)于MySQL中跨表排序和指定類型置頂?shù)乃姆N寫法詳解的文章就介紹到這了,更多相關(guān)MySQL跨表排序和固定置頂內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！