MySQL利用UNION连接2个查询排序失效详解

概述

UNION

连接数据集关键字,可以将两个查询结果集拼接为一个,会过滤掉相同的记录

UNION ALL

连接数据集关键字,可以将两个查询结果集拼接为一个,不会过滤掉相同的记录

今天在接到一个需求的时候使用了UNION进行查询后发现,如果两个查询分别使用ORDER BY后拼接居然无法成功排序,经过了好一番折腾,记录下

表结构及数据

-- 创建表
CREATE TABLE test_user (
 ID int(11) NOT NULL AUTO_INCREMENT,
 USER_ID int(11) DEFAULT NULL COMMENT '用户账号',
 USER_NAME varchar(255) DEFAULT NULL COMMENT '用户名',
 AGE int(5) DEFAULT NULL COMMENT '年龄',
 COMMENT varchar(255) DEFAULT NULL COMMENT '简介',
 PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- 数据插入语句
INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('1', '111', '开心菜鸟', '18', '今天很开心');
INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('2', '222', '悲伤菜鸟', '21', '今天很悲伤');
INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('3', '333', '认真菜鸟', '30', '今天很认真');
INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('4', '444', '高兴菜鸟', '18', '今天很高兴');
INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('5', '555', '严肃菜鸟', '21', '今天很严肃');

默认表数据显示如下

运行结果分析

-- 查询1
SELECT
 *
FROM
 test_user u
ORDER BY AGE

结果集1

-- 查询2
-- 使用UNION
(
 SELECT
  *
 FROM
  test_user u
 ORDER BY AGE
)
UNION
(
 SELECT
  *
 FROM
  test_user u
 ORDER BY AGE
);

-- 查询3
-- 使用UNION ALL
(
 SELECT
  *
 FROM
  test_user u
 ORDER BY AGE
)
UNION ALL
(
 SELECT
  *
 FROM
  test_user u
 ORDER BY AGE
)

结果集2:使用UNION

由于UNION会合并相同的记录(与DISTINCT实现相同效果),因此此处显示仅有5条记录

结果集3:使用UNION ALL

如果需要使用UNION ALL又需要进行排序,则要将其作为一个子查询来查

-- 查询4
-- 将UNION ALL作为子查询并进行排序
SELECT
 *
FROM
 (
 (
 SELECT
 *
 FROM
 test_user u
 ORDER BY
 AGE
 )
 UNION ALL
 (
 SELECT
 *
 FROM
 test_user u
 ORDER BY
 AGE
 )
 ) a
ORDER BY
 AGE;

结果集4

 改进

在经过一番搜索相关的经验后发现,是我之前有些画蛇添足了,原来可以在不使用子查询即可完成排序的方法:

-- 查询5
-- 第一个查询不使用排序,如果使用的话不加括号会报错(这也是我之前为什么会想用子查询的原因而没有想过这种方式了)
SELECT
 *
FROM
 test_user u

UNION ALL

SELECT
 *
FROM
 test_user u
ORDER BY
 AGE

运行出来的结果集与结果集4是相同的,此处就不再粘出结果。

结论

当我们使用UNION(或者UNION ALL)语句时,如果UNION的两个结果集在单独排序后再拼接,则他们的ORDER BY是失效的。如果我们要进行排序有以下两种方法:

  1. 将它们作为子查询再ORDER BY查询一次(还是建议使用方法2,子查询太不简洁了)
  2. 在第一个结果集中不使用排序,且不用括号分隔,而在第二个结果集后使用ORDER BY

参考链接

cnblogs:MySQL中UNION和UNION ALL的使用

总结

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对我们的支持。

(0)

相关推荐

  • 浅析mysql union和union all

    在数据库中,UNION和UNION ALL关键字都是将两个结果集合并为一个,但这两者从使用和效率上来说都有所不同. MySQL中的UNION UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果.实际大部分应用中是不会产生重复的记录,最常见的是过程表与历史表UNION.如: select * from gc_dfys union select * from ls_jg_dfys 这个SQL在运行时先取出两个表的结果,再用排序空间进行排

  • MySQL中使用or、in与union all在查询命令下的效率对比

    OR.in和union all 查询效率到底哪个快? 网上很多的声音都是说union all 快于 or.in,因为or.in会导致全表扫描,他们给出了很多的实例. 但真的union all真的快于or.in? EXPLAIN SELECT * from employees where employees.first_NAME ='Georgi' UNION ALL SELECT * from employees where employees.first_NAME ='Bezalel' 这条语

  • MySQL中union和join语句使用区别的辨析教程

    union和join是需要联合多张表时常见的关联词,具体概念我就不说了,想知道上网查就行,因为我也记不准确. 先说差别:union对两张表的操作是合并数据条数,等于是纵向的,要求是两张表字段必须是相同的(Schema of both sides of union should match.).也就是说如果A表中有三条数据,B表中有两条数据,那么A union B就会有五条数据.说明一下union 和union all的差别,对于union如果存在相同的数据记录会被合并,而union all不会合

  • Mysql联合查询UNION和Order by同时使用报错问题的解决办法

    因此,常常出现这样的错误 复制代码 代码如下: select * from [IND] where INDID>10unionselect * from [IND] where INDID<9 目前为止,还没有出现问 之后,也许有人会用到类似的查询 复制代码 代码如下: select * from [IND] where INDID>10 order by INDID descunionselect * from [IND] where INDID<9 order by INDID

  • Mysql联合查询UNION和UNION ALL的使用介绍

    一.UNION和UNION ALL的作用和语法 UNION 用于合并两个或多个 SELECT 语句的结果集,并消去表中任何重复行.UNION 内部的 SELECT 语句必须拥有相同数量的列,列也必须拥有相似的数据类型.同时,每条 SELECT 语句中的列的顺序必须相同.SQL UNION 语法: 复制代码 代码如下: SELECT column_name FROM table1UNIONSELECT column_name FROM table2 注释:默认地,UNION 操作符选取不同的值.如

  • MySQL UNION操作符基础知识点

    MySQL UNION 操作符 本教程为大家介绍 MySQL UNION 操作符的语法和实例. 描述 MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中.多个 SELECT 语句会删除重复的数据. 语法 MySQL UNION 操作符语法格式: SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] UNION [ALL | DISTINCT] S

  • 详解MySQL中UNION的用法

    如果想选择其他几个表中的行或从一个单一的表作为一个单独的结果集行的几个集会,那么可以使用的UNION. UNION在MySQL4.0以上版本才能可以使用.本节说明如何使用它. 假设有两个表,潜在和实际的客户列表,供应商购买耗材合并所有三个表中的姓名和地址,来创建一个单一的邮件列表.UNION提供了一种方法做到这一点.假设三个表有以下内容: mysql> SELECT * FROM prospect; +---------+-------+------------------------+ | f

  • mySQL UNION运算符的默认规则研究

    复制代码 代码如下: /* 建立数据表 */ create table td_base_data( id int(10) not null auto_increment,userId int(10) default '0',primary key (`id`))ENGINE=MyISAM DEFAULT CHARSET=gbk; create table td_base_data_20090527( id int(10) not null auto_increment,userId int(10

  • MySQL union 语法代码示例分析

    复制代码 代码如下: SELECT ... UNION [ALL | DISTINCT] SELECT ... [UNION [ALL | DISTINCT] SELECT ...] SELECT ... UNION [ALL | DISTINCT] SELECT ... [UNION [ALL | DISTINCT] SELECT ...] UNION 用于把来自许多SELECT语句的结果组合到一个结果集合中. (如果你要将多个表的查询结果进行合并输出比如说 群组消息跟个人消息表是分离的但是想

  • MySQL中union和order by同时使用的实现方法

    MySQL中union和order by是可以一起使用的,但是在使用中需要注意一些小问题,下面通过例子来说明.首先看下面的t1表. 1.如果直接用如下sql语句是会报错:Incorrect usage of UNION and ORDER BY. SELECT * FROM t1 WHERE username LIKE 'l%' ORDER BY score ASC UNION SELECT * FROM t1 WHERE username LIKE '%m%' ORDER BY score A

随机推荐