关于union的一些问题

创建一个测试数据表

CREATE TABLE `temp_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `endtime` int(10) NOT NULL,
  `basic_sort` smallint(8) NOT NULL,
  `type` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

对UNION 和UNION ALL 进行比较：

在MySQL 5.1中文手册中有下面一句话：

如果您对UNION不使用关键词ALL，则所有返回的行都是唯一的，如同您已经对整个结果集合使用了DISTINCT。如果您指定了ALL，您会从所有用过的SELECT语句中得到所有匹配的行。

插入测试数据：

INSERT INTO `temp_table` (`name`,`endtime`,`basic_sort`,`type`) VALUES ('a', '1534349307', '10', '2');
INSERT INTO `temp_table` (`name`,`endtime`,`basic_sort`,`type`) VALUES ('a', '1534349308', '14', '2');
INSERT INTO `temp_table` (`name`,`endtime`,`basic_sort`,`type`) VALUES ('c', '1534349309', '12', '1');
INSERT INTO `temp_table` (`name`,`endtime`,`basic_sort`,`type`) VALUES ('d', '1534349310', '12', '1');

 

(SELECT id,name,endtime,basic_sort,type from temp_table where type=1)
UNION 
(SELECT id,name,endtime,basic_sort,type from temp_table where type=2);

(SELECT id,name,endtime,basic_sort,type from temp_table where type=1)
UNION ALL
(SELECT id,name,endtime,basic_sort,type from temp_table where type=2);

 对于上面两句分别是UNION和UNION ALL组合，但是得到的结果是一致的。

其实上面说的【所有返回的行都是唯一的】这里所说的是主键唯一：

(SELECT id,name,endtime,basic_sort,type from temp_table)
UNION 
(SELECT id,name,endtime,basic_sort,type from temp_table);

(SELECT id,name,endtime,basic_sort,type from temp_table)
UNION ALL
(SELECT id,name,endtime,basic_sort,type from temp_table);

这里对于UNION返回的结果相当于对主键做一次DISTINCT，而UNION ALL 返回的结果相当于是单纯把结果查询出来然后合并返回。

对UNION查询结果进行排序：

插入测试数据（将刚刚的表内容删减，使用TRUNCATE `temp_table`）：

INSERT INTO `temp_table` (`name`,`endtime`,`basic_sort`,`type`) VALUES ('a', '1534349307', '10', '2');
INSERT INTO `temp_table` (`name`,`endtime`,`basic_sort`,`type`) VALUES ('b', '1534349308', '14', '2');
INSERT INTO `temp_table` (`name`,`endtime`,`basic_sort`,`type`) VALUES ('c', '1534349309', '12', '2');
INSERT INTO `temp_table` (`name`,`endtime`,`basic_sort`,`type`) VALUES ('d', '1534349310', '12', '2');
INSERT INTO `temp_table` (`name`,`endtime`,`basic_sort`,`type`) VALUES ('e', '1534349311', '18', '2');
INSERT INTO `temp_table` (`name`,`endtime`,`basic_sort`,`type`) VALUES ('f', '1534349312', '18', '2');
INSERT INTO `temp_table` (`name`,`endtime`,`basic_sort`,`type`) VALUES ('aa', '1534349313', '11', '1');
INSERT INTO `temp_table` (`name`,`endtime`,`basic_sort`,`type`) VALUES ('bb', '1534349314', '11', '1');
INSERT INTO `temp_table` (`name`,`endtime`,`basic_sort`,`type`) VALUES ('cc', '1534349315', '13', '1');
INSERT INTO `temp_table` (`name`,`endtime`,`basic_sort`,`type`) VALUES ('dd', '1534349316', '12', '1');
INSERT INTO `temp_table` (`name`,`endtime`,`basic_sort`,`type`) VALUES ('ee', '1534349317', '12', '1');
INSERT INTO `temp_table` (`name`,`endtime`,`basic_sort`,`type`) VALUES ('ff', '1534349318', '15', '1');

这里我们想要得到的结果是：按type的正序排序，接着按basic_sort倒叙排序

(select * from temp_table where type=1 ORDER BY basic_sort desc) 
union 
(select * from temp_table where type=2 ORDER BY basic_sort desc)

 对于这样的SQL语句得到的结果是：

与没有在子句中使用ORDER BY的结果一致。

(select * from temp_table where type=1) union (select * from temp_table where type=2)

 在MySQL 5.1中文手册中有下面两句话：

1、如果您想使用ORDER BY或LIMIT子句来对全部UNION结果进行分类或限制，则应对单个地SELECT语句加圆括号，并把ORDER BY或LIMIT放到最后一个的后面。

 如果要完成上面的需求应该是：

(select *,type as sort_type,basic_sort as sort_value from temp_table) 
union 
(select *,type as sort_type,basic_sort as sort_value from temp_table) 
order by sort_type ASC,sort_value ASC

2、圆括号中用于单个SELECT语句的ORDER BY只有当与LIMIT结合后，才起作用。否则，ORDER BY被优化去除。

(select * from temp_table WHERE type=1 ORDER BY basic_sort ASC LIMIT 6)
union 
(select * from temp_table WHERE type=2 ORDER BY basic_sort ASC LIMIT 6)

 

关于这里是可以使用ORDER BY 与LIMIT 共同完成，但是有些时候我们不能在里面用LIMIT 只能在外面用，比如我们做分页，我们并不知道应该在里面LIMIT 多少才对。

 

下面这里有个奇怪的需求，我们需要得到下面一个这个列表，首先是按type正序排列，当type为1的时候是按照basic_sort进行正序排列，当type为2的时候按endtime倒叙排列。

因为对于type为1的时候我们不需要对endtime进行排序所以我们在type为1的时候设置endtime的值为0，对于type为2的时候我们不需要basic_sort的值进行排序，所以设置basic_sort的值为0。

(SELECT id,name,endtime,basic_sort,basic_sort as sort_basic_sort,0 as sort_endtime,type,type as sort_type FROM temp_table WHERE type=1)
union 
(SELECT id,name,endtime,basic_sort,0 as sort_basic_sort,endtime as sort_endtime,type,type as sort_type FROM temp_table WHERE type=2)
ORDER BY sort_type ASC,sort_basic_sort ASC,sort_endtime DESC