Mysql 根据同一字段实现一条语句部分正序部分倒序的解决方案

前言

五个月没更新的我又回来了! 各位朋友六一快乐啊!

这次是朋友有一个比较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, 但这样就没办法分页了…放弃