MySQL UNION && UNION ALL
http://blog.itpub.net/29254281/viewspace-1190027/
http://my.oschina.net/xinxingegeya/blog/225646
建立如下表,
drop table test;
create table test(
id int not null auto_increment,
nickname varchar(20),
playNum varchar(20),
type int not null,
primary key (id)
);
insert into test(nickname,playNum,type) values('hello world',10,1);
insert into test(nickname,playNum,type) values('hello world',10,2);
insert into test(nickname,playNum,type) values('hello world',10,3);
insert into test(nickname,playNum,type) values('hello world',10,4);
insert into test(nickname,playNum,type) values('hello world',10,5);
insert into test(nickname,playNum,type) values('hello world',20,6);
type 分别为偶数 或 奇数 的查询结构,
mysql> select distinct nickname , playNum from test where type % 2 = 0;
+-------------+---------+
| nickname | playNum |
+-------------+---------+
| hello world | 10 |
| hello world | 20 |
+-------------+---------+
2 rows in set (0.00 sec)
mysql> select distinct nickname , playNum from test where type % 2 != 0;
+-------------+---------+
| nickname | playNum |
+-------------+---------+
| hello world | 10 |
+-------------+---------+
1 row in set (0.00 sec)
这两个查询结果的交集为 ,使用如下sql 查询,
select nickname ,playNum ,count(*)
from (select nickname , playNum from test where type % 2 = 0 union all select nickname , playNum from test where type % 2 != 0) temp
group by nickname , playNum having count(*) >1;
如下查询结果,
mysql> select nickname ,playNum ,count(*) from (select nickname , playNum from test where type % 2 = 0 union all select nickname , playNum from test where type % 2 != 0) temp group by nickname , pla
yNum having count(*) >1;
+-------------+---------+----------+
| nickname | playNum | count(*) |
+-------------+---------+----------+
| hello world | 10 | 5 |
+-------------+---------+----------+
1 row in set (0.00 sec)
UNION 和 UNION ALL的区别
UNION属于集合运算符(set operator)允许我们把多个表表达式组合到一个复合表表达式中,它把一个表表达式的结果放在另一个表表达式的下面。
在mysql数据库中提供了UNION和UNION ALL关键字,列于每个SELECT语句的对应位置的被选择的列应具有相同的类型。
在第一个SELECT语句中被使用的列名称也被用于结果的列名称。
如果UNION不使用关键词ALL,则所有返回的行都是唯一的,如同已经对整个结果集合使用了DISTINCT。
如果指定了ALL,则会从所有用过的SELECT语句中得到所有匹配的行。DISTINCT关键词是一个自选词,不起任何作用,但是根据SQL标准的要求,在语法中允许采用。也可以在同一查询中混合UNION ALL和UNION DISTINCT。被混合的UNION类型按照这样的方式对待,即DISTICT共用体覆盖位于其左边的所有ALL共用体。DISTINCT共用体可以使用UNION DISTINCT明确地生成,或使用UNION(后面不加DISTINCT或ALL关键词)隐含地生成。
还是如上面的表,union 查询和union all查询的区别,
mysql> select nickname , playNum from test where type % 2 = 0 union all select nickname , playNum from test where type % 2 != 0;
+-------------+---------+
| nickname | playNum |
+-------------+---------+
| hello world | 10 |
| hello world | 10 |
| hello world | 20 |
| hello world | 10 |
| hello world | 10 |
| hello world | 10 |
+-------------+---------+
6 rows in set (0.00 sec)
mysql> select nickname , playNum from test where type % 2 = 0 union select nickname , playNum from test where type % 2 != 0;
+-------------+---------+
| nickname | playNum |
+-------------+---------+
| hello world | 10 |
| hello world | 20 |
+-------------+---------+
2 rows in set (0.00 sec)
=============END=============