组合查询

如何用UNION操作符将多条SELECT语句组合成一个查询

组合查询

  • 并(union) 执行多个查询并将结果作为单个查询结果返回。
    一般需要使用组合查询的情况

  • 单个查询中从不同的表类似返回结果数据

  • 单个表执行多个查询，按单个查询返回数据

    创建组合查询

检索 IL,IN,MI几个洲的客户报表。

  1. mysql> SELECT cust_name, cust_contact, cust_email
  2.     -> FROM Customers
  3.     -> WHERE cust_state IN ('IL', 'IN', 'MI');
  4. +---------------+--------------+-----------------------+
  5. | cust_name     | cust_contact | cust_email            |
  6. +---------------+--------------+-----------------------+
  7. | Village Toys  | John Smith   | sales@villagetoys.com |
  8. | Fun4All       | Jim Jones    | jjones@fun4all.com    |
  9. | The Toy Store | Kim Howard   | NULL                  |
  10. +---------------+--------------+-----------------------+
  11. 3 rows in set (0.01 sec)

SELECT利用=符合，检索出所有Fun4All单位

  1. mysql> SELECT cust_name, cust_contact, cust_email
  2.     -> FROM Customers
  3.     -> WHERE cust_name = 'Fun4All';
  4. +-----------+--------------------+-----------------------+
  5. | cust_name | cust_contact       | cust_email            |
  6. +-----------+--------------------+-----------------------+
  7. | Fun4All   | Jim Jones          | jjones@fun4all.com    |
  8. | Fun4All   | Denise L. Stephens | dstephens@fun4all.com |
  9. +-----------+--------------------+-----------------------+
  10. 2 rows in set (0.00 sec)

把上面两条语句进行组合

  1. mysql> SELECT cust_name, cust_contact, cust_email
  2.     -> FROM Customers
  3.     -> WHERE cust_state IN ('IL','IN','MI')
  4.     -> UNION
  5.     -> SELECT cust_name, cust_contact, cust_email
  6.     -> FROM Customers
  7.     -> WHERE cust_name = 'Fun4All';
  8. +---------------+--------------------+-----------------------+
  9. | cust_name     | cust_contact       | cust_email            |
  10. +---------------+--------------------+-----------------------+
  11. | Village Toys  | John Smith         | sales@villagetoys.com |
  12. | Fun4All       | Jim Jones          | jjones@fun4all.com    |
  13. | The Toy Store | Kim Howard         | NULL                  |
  14. | Fun4All       | Denise L. Stephens | dstephens@fun4all.com |
  15. +---------------+--------------------+-----------------------+
  16. 4 rows in set (0.02 sec)

解释: 中介有UNION分割开，并把输出组合成儿一个查询结果

另一个检索

  1. mysql> SELECT cust_name, cust_contact, cust_email
  2.     -> FROM Customers
  3.     -> WHERE cust_state IN('IL','IN','MI')
  4.     -> OR cust_name = 'Fun4All';
  5. +---------------+--------------------+-----------------------+
  6. | cust_name     | cust_contact       | cust_email            |
  7. +---------------+--------------------+-----------------------+
  8. | Village Toys  | John Smith         | sales@villagetoys.com |
  9. | Fun4All       | Jim Jones          | jjones@fun4all.com    |
  10. | Fun4All       | Denise L. Stephens | dstephens@fun4all.com |
  11. | The Toy Store | Kim Howard         | NULL                  |
  12. +---------------+--------------------+-----------------------+
  13. 4 rows in set (0.00 sec)

使用UNION规则

  • 必须有两条以上SELECT语句组合，语句直接用关键字UNION分割。
  • UNION中每个查询必须包含相同的列，表单式，聚集函数。
  • 列的数据必须兼容，

    是否带有重复行

UNION默认去掉重复行

如果想要所有行，可以使用UNION ALL 而不是UNION。

  1. mysql> SELECT cust_name, cust_contact, cust_email
  2.     -> FROM Customers
  3.     -> WHERE cust_state IN ('IL','IN','MI')
  4.     -> UNION ALL
  5.     -> SELECT cust_name, cust_contact, cust_email
  6.     -> FROM Customers
  7.     -> WHERE cust_name = 'Fun4All';
  8. +---------------+--------------------+-----------------------+
  9. | cust_name     | cust_contact       | cust_email            |
  10. +---------------+--------------------+-----------------------+
  11. | Village Toys  | John Smith         | sales@villagetoys.com |
  12. | Fun4All       | Jim Jones          | jjones@fun4all.com    |
  13. | The Toy Store | Kim Howard         | NULL                  |
  14. | Fun4All       | Jim Jones          | jjones@fun4all.com    |
  15. | Fun4All       | Denise L. Stephens | dstephens@fun4all.com |
  16. +---------------+--------------------+-----------------------+
  17. 5 rows in set (0.00 sec)

与上面例子比多了一行。

对组合查询结果排序

  1. mysql> SELECT cust_name, cust_contact, cust_email
  2.     -> FROM Customers
  3.     -> WHERE cust_state IN ('IL''IN','MI')
  4.     -> UNION
  5.     -> SELECT cust_name, cust_contact, cust_email
  6.     -> FROM Customers
  7.     -> WHERE cust_name = 'Fun4ALL'
  8.     -> ORDER BY cust_name, cust_contact;
  9. +--------------+--------------------+-----------------------+
  10. | cust_name    | cust_contact       | cust_email            |
  11. +--------------+--------------------+-----------------------+
  12. | Fun4All      | Denise L. Stephens | dstephens@fun4all.com |
  13. | Fun4All      | Jim Jones          | jjones@fun4all.com    |
  14. | Village Toys | John Smith         | sales@villagetoys.com |
  15. +--------------+--------------------+-----------------------+
  16. 3 rows in set (0.00 sec)

原文: https://cxiaodian.gitbooks.io/mysql/content/chapter13.html