MySQL 正负数排序

一、数据准备

建表如下：

CREATE TABLE `tab` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

新增数据：

INSERT INTO `tab`(`id`, `num`) VALUES (1, '1');
INSERT INTO `tab`(`id`, `num`) VALUES (2, '2');
INSERT INTO `tab`(`id`, `num`) VALUES (3, '3');
INSERT INTO `tab`(`id`, `num`) VALUES (4, '4');
INSERT INTO `tab`(`id`, `num`) VALUES (5, '5');
INSERT INTO `tab`(`id`, `num`) VALUES (6, '-1');
INSERT INTO `tab`(`id`, `num`) VALUES (7, '-2');
INSERT INTO `tab`(`id`, `num`) VALUES (8, '-3');
INSERT INTO `tab`(`id`, `num`) VALUES (9, '-4');
INSERT INTO `tab`(`id`, `num`) VALUES (10, '-5');
INSERT INTO `tab`(`id`, `num`) VALUES (11, '--');
INSERT INTO `tab`(`id`, `num`) VALUES (12, '--');

如图：

二、正负数排序

1️⃣正确写法

select * from tab ORDER BY num='--',convert(num,DECIMAL(10,2)) desc;
select * from tab ORDER BY num='--',cast(num as DECIMAL(10,2)) desc;
select * from tab ORDER BY num='--',num+0 desc;

2️⃣常规排序：只有正数部分倒序排列，负数部分不符合要求

3️⃣union 会导致某部分数据排序紊乱

三、union 相关

如果直接用如下sql语句是会报错：Incorrect usage of UNION and ORDER BY。

select * from t1 where name like 'l%' order by score asc
union
select * from t1 where name like '%m%' order by score asc

因为union在没有括号的情况下只能使用一个order by，所以报错。这个语句有2种修改方法。如下：

1️⃣可以将前面一个order by去掉，改成如下：

select * from t1 where name like 'l%' 
union
select * from t1 where name like '%m%' order by score asc

该sql的意思就是先union，然后对整个结果集进行order by。

2️⃣可以通过两个查询分别加括号的方式，改成如下：

(select * from t1 where name like 'l%' order by score asc)
union
(select * from t1 where name like '%m%' order by score asc)

这种方式的目的是为了让两个结果集先分别order by，然后再对两个结果集进行union。这种方式虽然不报错，但是两个order by并没有效果，应该改成如下：

select * from
(select * from t1 where name like 'l%' order by score asc) t3
union
select * from
(select * from t1 where name like '%m%' order by score asc) t4

也就是说，order by不能直接出现在union的子句中，但是可以出现在子句的子句中。上面排序的sql也可以写为如下：

3️⃣顺便提一句，union和union all的区别：union 会过滤掉两个结果集中重复的行，而 union all 不会过滤掉重复行。