union的用法

union的用法
union用来连接两个查询语句，把两个查询语句的查询结果合并起来，两个查询语句的查询字段个数必须一样，否则会出错，查询的字段可以不一样，类型也可以不一样，但是这样查询的意义不大，如果查询的字段不一样，最终的结果集以前者查询的字段为准。如果用union进行连接，碰到所有字段值一样的列，就会合并，去掉重复的行，如果用union all进行连接，则不会去掉重复的内容，所有的内容都被取出。


mysql> (select goods_id,goods_name,cat_id from goods where cat_id=2 order by goo
ds_id asc)
    -> union (select goods_id,goods_name,cat_id from goods where cat_id=4 order
by goods_id desc);
+----------+--------------+--------+
| goods_id | goods_name   | cat_id |
+----------+--------------+--------+
|       16 | 恒基伟业G101       |      2 |
|        1 | KD876        |      4 |
|       14 | 诺基亚5800XM      |      4 |
|       18 | 夏新T5           |      4 |
+----------+--------------+--------+
4 rows in set (0.00 sec)
#在每个查询语句中进行排序，再进行连接，其中的排序就没有意义，所以mysql回把排序的语句进行优化掉


mysql> select goods_id,cat_id,goods_name from goods where cat_id=2
    -> union
    -> select goods_id,cat_id,goods_name from goods where cat_id=4
    -> order by goods_id;
+----------+--------+--------------+
| goods_id | cat_id | goods_name   |
+----------+--------+--------------+
|        1 |      4 | KD876        |
|       14 |      4 | 诺基亚5800XM      |
|       16 |      2 | 恒基伟业G101       |
|       18 |      4 | 夏新T5           |
+----------+--------+--------------+
4 rows in set (0.00 sec)

mysql> #排序放在最后是针对最后的结果集进行排序，有意义，


mysql> (select goods_id,cat_id,goods_name from goods where cat_id=3 order by goo
ds_id limit 3)
    -> union
    -> (select goods_id,cat_id,goods_name from goods where cat_id=4 order by goo
ds_id limit 2)
    -> ;
+----------+--------+--------------+
| goods_id | cat_id | goods_name   |
+----------+--------+--------------+
|        8 |      3 | 飞利浦9@9v         |
|        9 |      3 | 诺基亚E66         |
|       10 |      3 | 索爱C702c        |
|        1 |      4 | KD876        |
|       14 |      4 | 诺基亚5800XM      |
+----------+--------+--------------+
5 rows in set (0.05 sec)

#这次排序影响的最终的显示结果有意义，所以order by 起了作用

一道面试题
mysql> create table a (
    -> id char(1),
    -> num int
    -> )engine myisam charset utf8;
Query OK, 0 rows affected (0.12 sec)

mysql> insert into a values ('a',5),('b',10),('c',15),('d',10);
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> create table b (
    -> id char(1),
    -> num int
    -> )engine myisam charset utf8;
Query OK, 0 rows affected (0.04 sec)

mysql> 
mysql> insert into b values ('b',5),('c',15),('d',20),('e',99);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0


#将两张表合并，相同id的num相加
mysql> select id,sum(num) from (select * from a union all select * from b) as temp group by id;
+------+----------+
| id   | sum(num) |
+------+----------+
| a    |        5 |
| b    |       15 |
| c    |       30 |
| d    |       30 |
| e    |       99 |
+------+----------+
5 rows in set (0.05 sec)
#将两张表用union all合并，然后再按照id分组，求和，达到最终的目的