前言
五个月没更新的我又回来了! 各位朋友六一快乐啊!
这次是朋友有一个比较emmm罕见的需求吧, 场景类似活动时间吧, 活动结束的倒序, 正在进行的正序, 看看sql应该怎么写
环境
Mysql版本
Mysql 5.7
准备表和数据
简单来个表, 主要就是按时间部分正序部分倒序,
CREATE TABLE `test_order` ( `id` int(11) NOT NULL AUTO_INCREMENT, `timer` int(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4; INSERT INTO `test_order`(`id`, `timer`) VALUES (1, 10); INSERT INTO `test_order`(`id`, `timer`) VALUES (2, 20); INSERT INTO `test_order`(`id`, `timer`) VALUES (3, 30); INSERT INTO `test_order`(`id`, `timer`) VALUES (4, 40);
想实现的是 timer <= 20 asc, timer >20 desc, 预期结果 10, 20, 40, 30
实现方案
两种实现方法思路都是一样的
实现方案一(Case)
SELECT *, CASE WHEN timer > 20 THEN 2 ELSE 1 END AS group_id, CASE WHEN timer > 20 THEN timer * ( - 1 ) ELSE timer END AS sort FROM test_order ORDER BY group_id ASC, sort ASC;
简单说一下实现方式, 就是先按照 >20 和 <=20 给一个强排(1 > 2), 然后在对 >20的乘个-1, 改变正排序, 结果符合预期
实现方案二(If)
SELECT * FROM test_order ORDER BY IF ( timer > 20, 0, 1 ) DESC, IF ( timer <= 20, timer, timer *- 1 ) ASC;
其他未实现方案
多条件排序
SELECT * FROM test_order ORDER BY timer>20 ASC, timer<=20 ASC; // 10, 20, 30, 40 SELECT * FROM test_order ORDER BY timer>20 ASC, timer<=20 DESC; // 10, 20, 30, 40 SELECT * FROM test_order ORDER BY timer>20 DESC, timer<=20 ASC; // 30, 40, 10, 20 SELECT * FROM test_order ORDER BY timer>20 DESC, timer<=20 DESC; // 30, 40, 10, 20
这是因为 order 判断结果为1和0, 然后默认按id排序, 把id=3改成5, 结果就是
union
先直接拼两条语句
SELECT * FROM test_order WHERE timer < 20 ORDER BY timer ASC UNION SELECT * FROM test_order WHERE timer >= 20 ORDER BY timer DESC;
报错 1221 - Incorrect usage of UNION and ORDER BY; 加别名解决
SELECT * FROM ( SELECT * FROM test_order WHERE timer < 20 ORDER BY timer ASC ) AS one UNION ALL SELECT * FROM ( SELECT * FROM test_order WHERE timer >= 20 ORDER BY timer DESC ) AS two;
子句的order没有生效, 看下官方文档
子句必须要配合limit才可以使用order, 但这样就没办法分页了…放弃
