MySQL如何根据自定义权重数据集排序并满足价格筛选查询需求

你现有商品表（假设表名为product）结构与数据如下：

需要基于自定义权重规则[{id:2,weight:8},{id:5,weight:6},{id:4,weight:3}]，筛选price <5的商品，优先按权重升序排序，无匹配权重的记录按price降序排序，可直接使用以下查询语句：

SELECT id, product_id, price
FROM product
WHERE price < 5
ORDER BY
  -- 映射自定义权重，无权重记录赋值远大于所有现有权重的默认值，保证排在有权重记录之后
  CASE id
    WHEN 2 THEN 8
    WHEN 5 THEN 6
    WHEN 4 THEN 3
    ELSE 9999
  END ASC,
  price DESC;

如果自定义权重条目较多，推荐使用临时权重表关联查询，方便维护：

-- 创建临时权重表存储自定义权重
CREATE TEMPORARY TABLE IF NOT EXISTS weight_map(
    id INT PRIMARY KEY,
    weight INT NOT NULL
);
-- 插入权重数据
INSERT INTO weight_map(id, weight) VALUES (2,8), (5,6), (4,3);

-- 关联查询排序
SELECT p.id, p.product_id, p.price
FROM product p
LEFT JOIN weight_map w ON p.id = w.id
WHERE p.price < 5
ORDER BY
  IFNULL(w.weight, 9999) ASC,
  p.price DESC;

执行后返回结果顺序与你预期一致：id为4、2、3、1的四条记录。

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