17.MySQL之组合查询（union）

如果想看其他有关于MySQL数据库的文章，请跳转到到MySQL自学目录

本节讲述组合查询union的用法及注意事项

为了大家更好的理解，我将例子中需要的sql文件上传到了百度网盘，供大家食用。
链接：https://pan.baidu.com/s/1dqQS2sQj8QoCKi3UfN1wVA 密码：h7hh

union

1. 什么时候会使用到union？

1. 在单个查询中从不同的表返回类似的结构的数据
2. 对单个表执行多个查询，按单个查询返回数据

2. union是干嘛的？

使用union可以将多个select语句的查询结果集合并起来。

3. union的使用方法

很简单！只需要把union放在每个select之间
举个栗子

mysql> # union用法
mysql> # 取出生产商为1002和1005的生产的产品
mysql> select vend_id,prod_name,prod_price from products where vend_id =1002
    -> union
    -> select vend_id,prod_name,prod_price from products where vend_id =1005;
+---------+--------------+------------+
| vend_id | prod_name    | prod_price |
+---------+--------------+------------+
|    1002 | Fuses        |       3.42 |
|    1002 | Oil can      |       8.99 |
|    1005 | JetPack 1000 |      35.00 |
|    1005 | JetPack 2000 |      55.00 |
+---------+--------------+------------+
4 rows in set (2.18 sec)

4. union的规则

• union必须由两条或两条以上的select语句组成，语句之间用关键字union分隔（若有4条select语句，则使用3个union关键字）
• union每个查询必须包含相同的列数，且列数据类型必须兼容，但必须是DBMS可以隐含地转换的类型。
• 多次SQL语句取出的列名可以不一致，此时以第一次的列名为准。
  举个栗子

mysql> # union规则
mysql> # 从products表中取出生产商为1002和从orderitems表中取出order_num 为20008的订单
mysql> # products取出的列为vend_id与prod_price,orderitems表中取出的列为order_num与item_price
mysql> select vend_id,prod_price from products where vend_id =1002
    -> union
    -> select order_num,item_price from orderitems where order_num =20008;
+---------+------------+
| vend_id | prod_price |
+---------+------------+
|    1002 |       3.42 |
|    1002 |       8.99 |
|   20008 |       2.50 |
+---------+------------+
3 rows in set (0.10 sec)

5. union all

当我们列出的两行是一样的，union只会显示一行，会去除重复的行。想要匹配所有的行，就用union all。举个栗子

mysql> # 从products取出生产商为1001最贵的两件商品和取出生产商为1003最便宜的两件商品
mysql> (select vend_id,prod_price from products where vend_id =1001 order by prod_price desc limit 2)
    -> union
    -> (select vend_id,prod_price from products where vend_id =1003 order by prod_price limit 2);
+---------+------------+
| vend_id | prod_price |
+---------+------------+
|    1001 |      14.99 |
|    1001 |       9.99 |
|    1003 |       2.50 |
+---------+------------+
3 rows in set (0.00 sec)

mysql> # 从products取出生产商为1001最贵的两件商品和取出生产商为1003最便宜的两件商品
mysql> (select vend_id,prod_price from products where vend_id =1001 order by prod_price desc limit 2)
    -> union all
    -> (select vend_id,prod_price from products where vend_id =1003 order by prod_price limit 2);
+---------+------------+
| vend_id | prod_price |
+---------+------------+
|    1001 |      14.99 |
|    1001 |       9.99 |
|    1003 |       2.50 |
|    1003 |       2.50 |
+---------+------------+
4 rows in set (0.10 sec)

6. 对组合结构查询排序

• 如果子句中有order by,limit,需要加小括号。推荐放到所有子句之后，即对最终合并后的结果来排序。
• order by与limit配合起来使用才有意义。如果不配合limit使用时，会被语法分析器优化时去除。

mysql> # 从products取出生产商为1001最贵的两件商品和取出生产商为1003最便宜的两件商品,并对结果进行排序
mysql> (select vend_id,prod_price from products where vend_id =1003 order by prod_price desc limit 2)
    -> union all
    -> (select vend_id,prod_price from products where vend_id =1001 order by prod_price desc limit 2)
    -> order by prod_price desc;
+---------+------------+
| vend_id | prod_price |
+---------+------------+
|    1003 |      50.00 |
|    1001 |      14.99 |
|    1003 |      13.00 |
|    1001 |       9.99 |
+---------+------------+
4 rows in set (0.16 sec)

mysql> select vend_id,prod_price from products where vend_id =1001 order by prod_price desc limit 2
    -> union all
    -> select vend_id,prod_price from products where vend_id =1001 order by prod_price desc limit 2;
ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY