关于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