Mysql中UNION用法与排序

在mysql数据库中UNION是联合查询了,它分UNION与UNION all了,那它们有什么区别怎么用呢,下面我来给各位同学详细道来吧.

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

一、UNION 用法,UNION语法如下:

1. SELECT ...    
2. UNION[ALL | DISTINCT]    
3. SELECT ...    
4. [UNION [ALL | DISTINCT]    
5. SELECT ...]   

UNION用于把来自许多SELECT语句的结果组合到一个结果集合中,列于每个SELECT语句的对应位置的被选择的列应具有相同的类型,例如,被第一个语句选择的第一列应和被其它语句选择的第一列具有相同的类型,在第一个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放到最后一个的后面。以下例子同时使用了这两个子句,代码如下:

1. (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;    
2.  
3.  (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 子句必须引用别名,而不能引用列名称,以下语句中的第一个语句必须运行,但是第二个会运行失败,出现在'order clause'中有未知列'a'的错误,代码如下:

1. (SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b;  
2.  
3. (SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a;  
4.  
5. To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT。 

为了对单个SELECT使用ORDER BY或LIMIT,应把子句放入圆括号中,圆括号包含了SELECT,代码如下:

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

二 实例扩展 

union可以对同一个表的两次查询联合起来,这样做的益处也非常明显,比如在blog应用中,可以利用一条sql语句实现置顶blog和普通blog的分页显示,代码如下:

1. (    
2. SELECT * FROM `blog` 
3. WHERE top=1    
4. ORDER BY created DESC    
5. )    
6. UNION 
7. (   
8. SELECT *    
9. FROM `blog`    
10. WHERE top = 0    
11. ORDER BY created DESC    
12. ) LIMIT 2 , 3 

注:union要求联合的两个表所要查找的数据列要一样多,如果一个表中没有另一个表的字段,可以用NULL代替.

三,实例,UNION,代码如下:

1. select * from  
2. (  
3. select * from ...  
4. union all  
5. select * from ...  
6. ) aaa  
7. order by aaa.xxx desc; 

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

解决mysql中union排序的问题:

关于union双向排序(mysql),群里的朋友问了一个mysql查询的问题,需求是这样的,论坛希望在显示帖子列表时侯前三条是点击量最多的,即按点击次数查询,余下的按时间查询,实际就是两个union的排序都生效,Sql代码如下:

1. (select * from (select * from table order by colnum desc) a);   
2. (select * from table order by colnum limit 0,2 
3. ) 
4. UNION  (select * from (select * from table order by colnum desc) a); 

估计很多人会有这个需求,代码如下:

1. SELECT *,1 as ord FROM `women` where status = 2 and title like '%2014%' or title like '%Vevb.com%' union (select *,2 as ord FROM `women` where status = 2 and keywords like '%2014%' or keywords like '%Vevb.com%') ORDER BY ord,id desc limit 0,15;