数据库多维度order by 排序分析

案例

开发一个类似与咸鱼app的产品，其中有个需求是在登录用户打开app时，首页展示其他人发布的商品列表，
商品信息中包含商品信息、发布时间、发货地点，取货方式等多个字段，现有需求：展示信息优先将同城且支持上门的需求按最新发布时间排序
其次显示同城支持快递的，然后是不同城支持快递的，最后是不同城且不支持快递的，每页数据20条进行分页。

表结构

CREATE TABLE `t_order` (
  `orderId` INT(11) NOT NULL AUTO_INCREMENT,
  `userId` INT(11) NOT NULL,
  `nickname` VARCHAR(255) NOT NULL,
  `title` VARCHAR(40) NOT NULL,
  `logistics` TINYINT(1) NOT NULL,        -- 物流取货方式 3 上门取件 2 快递 1 其他 
  `area` BIGINT(20) NOT NULL COMMENT '0', -- 发货地点编码
  `address` VARCHAR(255) NOT NULL,
  `mobile` VARCHAR(20) NOT NULL,
  `isPublic` TINYINT(1) UNSIGNED ZEROFILL NOT NULL DEFAULT '0',
  `wechat` VARCHAR(30) NOT NULL,
  `status` TINYINT(1) NOT NULL,
  `pushTime` INT(11) NOT NULL,
  `offerType` TINYINT(1) NOT NULL,
  `isdetele` TINYINT(1) NOT NULL,
  `updateTime` TIMESTAMP NULL DEFAULT NULL,
  `successTime` INT(11) DEFAULT NULL,
  PRIMARY KEY (`demandId`)
) ENGINE=INNODB AUTO_INCREMENT=194 DEFAULT CHARSET=utf8; 

需求分析

需求描述中共需要三个字段排序 发货地点 取货方式 发布时间 其中 发货地点首先同城其次不同城排序，发布时间降序排序 取货方式排序规则比较
复杂，它依赖与发货地点 同城时 以“上门取件 > 快递 > 其他”进行排序 非同城时以“快递>上门取件>其他”排序。
此时简单的以 order by 字段一 desc , 字段一 desc ,字段一 desc 进行排序已经不能实现这个需求了。
我们就需要将不同维度进行组合，将不同条件下的维度进行合并成一个统一的维度，划分权重，然后排序，就能很方便的实现这个需求，
经分析，该需求下发货地点，取货方式维度权重划分如下：

  同城&&上门    权重 5
  同城&&快递    权重 4
  同城&&其他    权重 3
  非同城&&快递   权重 2
  非同城&&上门   权重 1
  非同城&&其他   权重 0

这样排序时就可以这样写，以权重降序就行了

-- logistics  3 上门 2 快递   1 其他 
SELECT 
(CASE   WHEN s.area = 1002000000 AND s.logistics = 3 THEN 5 
    WHEN s.area = 1002000000 AND s.logistics = 2 THEN 4
    WHEN s.area = 1002000000  AND s.logistics = 1 THEN 3
    WHEN   s.logistics = 2 THEN 2 
    WHEN   s.logistics = 3 THEN 1 
    WHEN   s.logistics = 1 THEN 0 END) weights,
s.area,s.logistics,s.updatetime ,s.* 
FROM t_orders ORDER BY weights DESC  ,updatetime DESC , orderId DESC LIMIT 0,20;

查询结果如图

后文

我在上面sql排序的时候除了用需求中提的三个维度外，还用了商品表的主键排序，这么做的主要原因是为了避免当
order by的字段有多个行都有相同的值，mysql是会随机的顺序返回查询结果的问题具体可看
以下文章
Mysql order by与limit混用陷阱