【转载】Mysql中的Union与Uninon ALL

近日,在一个项目中用到union组合两个select结果,调试sql时总是报错,所报错误也只是说在union附近有问题,因为sql中用到了group,我想也许是union不支持吧,由于时间紧,就先在程序中做了合并处理。但程序员对于代码的完美性要求总是不能放弃的,所以,常常会有如鲠在喉的感觉,不搞明白心里会不舒服。
仔细查看了MS的在线帮助,在有关UNION的描述中找到这样一段说明: 如果使用
UNION 运算符,那么各个 SELECT 语句不能包含它们自己的 ORDER BY 或 COMPUTE
子句。而只能在最后一个 SELECT 语句的后面使用一个 ORDER BY 或 COMPUTE
子句;该子句适用于最终的组合结果集。只能在各个 SELECT 语句中指定 GROUP
BY 和 HAVING 子句。
在这段说明中并没有说union不能用于group,而是说在各个Select中不能包含order
by,而在我的语句中恰恰就有这个,看来是我猜错了。试了一下把order去掉,果然不会报错了。也就是说,使用union的时候,各查询group是可以的,但不能order或compute。那如果说非要group,有没有办法呢?正常情况下只能在最后使用,而且是针对组合后的结果集进行排序的,而我刚才所说的group,就不能用于最终结果集,而只能用于每个查询。
这是正常的用法,有些人想在每个查询中先排序,然后再union,也有非正常的用法,类似:
select * from (select a from [table] order by a) union …
另外,union后面还可以加上all,在默认情况下,union时会删除重复的项,如果加上all则不进行筛选,组合所有的结果。如果能确定各查询结果不会有重复的项,最好就带上all,因为这样还是可以提高一些效率的。

   最近也是在写项目中碰到的这个问题,需要将两个SELECT查询结果组合起来进行分组排序,想到了用union方法,用TP的union操作根本无法完成复杂的union操作,于是搜罗了一下,先说一下union的用法,在说一下TP中如何实现复杂的union操作。

原文MySQL中UNION和UNION ALL的使用

在数据库中,UNION和UNION
ALL关键字都是将两个结果集合并为一个,但这两者从使用和效率上来说都有所不同。

MySQL中的UNION

UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录,最常见的是过程表与历史表UNION。如:

select * from gc_dfys union select * from ls_jg_dfys

这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。 

MySQL中的UNION ALL

而UNION
ALL只是简单的将两个结果合并后就返回。这样,如果返回的两个结果集中有重复的数据,那么返回的结果集就会包含重复的数据了。

从效率上说,UNION ALL
要比UNION快很多,所以,如果可以确认合并的两个结果集中不包含重复的数据的话,那么就使用UNION
ALL,如下:

select * from gc_dfys union all select * from ls_jg_dfys

 

使用Union,则所有返回的行都是唯一的,如同您已经对整个结果集合使用了DISTINCT
使用Union all,则不会排重,返回所有的行

如果您想使用ORDER
BY或LIMIT子句来对全部UNION结果进行分类或限制,则应对单个地SELECT语句加圆括号,并把ORDER
BY或LIMIT放到最后一个的后面:
(SELECT a FROM tbl_name WHERE a=10 AND B=1)  
UNION
(SELECT a FROM tbl_name WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;
麻烦一点也可以这么干:
select userid from (
select userid from testa union all select userid from testb) t 
order by userid limit 0,1;

如果你还想group by,而且还有条件,那么:
select userid from (select userid from testa union all select userid
from testb) t group by userid having count(userid) = 2;

注意:在union的括号后面必须有个别名,否则会报错

当然了,如果当union的几个表的数据量很大时,建议还是采用先导出文本,然后用脚本来执行
因为纯粹用sql,效率会比较低,而且它会写临时文件,如果你的磁盘空间不够大,就有可能会出错
Error writing file ‘/tmp/MYLsivgK’ (Errcode: 28)

  一、UNION 用法

  一 UNION语法

  SELECT …

  UNION[ALL | DISTINCT]

  SELECT …

  [UNION [澳门金沙vip,ALL | DISTINCT]

  SELECT …]

  UNION用于把来自许多SELECT语句的结果组合到一个结果集合中。

  列于每个SELECT语句的对应位置的被选择的列应具有相同的类型。(例如,被第一个语句选择的第一列应和被其它语句选择的第一列具有相同的类型。www.111cn.net)在第一个SELECT语句中被使用的列名称也被用于结果的列名称。

  SELECT语句为常规的选择语句,但是受到如下的限定:

  ·只有最后一个SELECT语句可以使用INTO OUTFILE。

  ·HIGH_PRIORITY不能与作为UNION一部分的SELECT语句同时使用。如果您对第一个
SELECT指定了HIGH_PRIORITY,则不会起作用。如果您对其它后续的SELECT语句指定了HIGH_PRIORITY,则会产生语法错误。

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

  DISTINCT关键词是一个自选词,不起任何作用,但是根据SQL标准的要求,在语法中允许采用。(在MySQL中,DISTINCT代表一个共用体的默认工作性质。)

  您可以在同一查询中混合UNION ALL和UNION
DISTINCT。被混合的UNION类型按照这样的方式对待,即DISTINCT共用体覆盖位于其左边的所有ALL共用体。DISTINCT共用体可以使用UNION
DISTINCT明确地生成,或使用UNION(后面不加DISTINCT或ALL关键词)隐含地生成。

  如果您想使用ORDER
BY或LIMIT子句来对全部UNION结果进行分类或限制,则应对单个地SELECT语句加圆括号,并把ORDER
BY或LIMIT放到最后一个的后面。以下例子同时使用了这两个子句:

 代码如下

 

(SELECT a FROM tbl_name WHERE a=10 AND B=1)UNION(SELECT a FROM
tbl_name WHERE a=11 AND B=2)ORDER BY a LIMIT 10;   

 (SELECT a FROM tbl_name WHERE a=10 AND B=1)UNION(SELECT a FROM
tbl_name WHERE a=11 AND B=2)ORDER BY a LIMIT 10;

  这种ORDER
BY不能使用包括表名称(也就是,采用tbl_name.col_name格式的名称)列引用。可以在第一个SELECT语句中提供一个列别名,并在ORDER
BY中参阅别名,或使用列位置在ORDER
BY中参阅列。(首选采用别名,因为不建议使用列位置。)

  另外,如果带分类的一列有别名,则ORDER BY
www.111cn.nEt子句必须引用别名,而不能引用列名称。以下语句中的第一个语句必须运行,但是第二个会运行失败,出现在’order
clause’中有未知列’a’的错误:

 代码如下