第13章、组合查询

标签： MySQL是怎样使用的新版

我们前边说的都是单条查询语句，其实多条查询语句产生的结果集查也可以被合并成一个大的结果集，这种将多条查询语句产生的结果集合并起来的查询方式称为合并查询，或者组合查询。

涉及单表的组合查询

比方说我们有下边两个查询语句：

  1. mysql> SELECT m1 FROM t1 WHERE m1 < 2;
  2. +------+
  3. | m1   |
  4. +------+
  5. |    1 |
  6. +------+
  7. 1 row in set (0.00 sec)
  9. mysql> SELECT m1 FROM t1 WHERE m1 > 2;
  10. +------+
  11. | m1   |
  12. +------+
  13. |    3 |
  14. +------+
  15. 1 row in set (0.00 sec)
  17. mysql>

如果我们想把上边两个查询语句的结果集合并到一个大的结果集中，最简单的方式当然是使用OR操作符把两个查询语句中的搜索条件连起来，就像这样：

  1. mysql> SELECT m1 FROM t1 WHERE m1 < 2 OR m1 > 2;
  2. +------+
  3. | m1   |
  4. +------+
  5. |    1 |
  6. |    3 |
  7. +------+
  8. 2 rows in set (0.00 sec)
  10. mysql>

除了这种方式，我们还可以使用UNION来将两个查询语句连在一起，就像这样：

  1. mysql> SELECT m1 FROM t1 WHERE m1 < 2 UNION SELECT m1 FROM t1 WHERE m1 > 2;
  2. +------+
  3. | m1   |
  4. +------+
  5. |    1 |
  6. |    3 |
  7. +------+
  8. 2 rows in set (0.01 sec)
  10. mysql>

多个查询语句也直接用UNION来连起来：

  1. mysql> SELECT m1 FROM t1 WHERE m1 < 2 UNION SELECT m1 FROM t1 WHERE m1 > 2 UNION SELECT m1 FROM t1 WHERE m1 = 2;
  2. +------+
  3. | m1   |
  4. +------+
  5. |    1 |
  6. |    3 |
  7. |    2 |
  8. +------+
  9. 3 rows in set (0.00 sec)
  11. mysql>

当然，并不是说使用UNION连接起来的各个查询语句的查询列表处只能有一个表达式，有多个表达式也是可以的，只要数量相同就可以了，比如下边这个使用UNION连接起来的各个查询语句的查询列表处都有2个表达式：

  1. mysql> SELECT m1, n1 FROM t1 WHERE m1 < 2 UNION SELECT m1, n1 FROM t1 WHERE m1 > 2;
  2. +------+------+
  3. | m1   | n1   |
  4. +------+------+
  5. |    1 | a    |
  6. |    3 | c    |
  7. +------+------+
  8. 2 rows in set (0.00 sec)
  10. mysql>

不过这里需要注意一点，设计MySQL的大叔建议：使用UNION连接起来的各个查询语句的查询列表中位置相同的表达式的类型应该是相同的。当然这不是硬性要求，如果不匹配的话，MySQL将会自动的进行类型转换，比方说下边这个组合查询语句：

  1. mysql> SELECT m1, n1 FROM t1 WHERE m1 < 2 UNION SELECT n1, m1 FROM t1 WHERE m1 > 2;
  2. +------+------+
  3. | m1   | n1   |
  4. +------+------+
  5. | 1    | a    |
  6. | c    | 3    |
  7. +------+------+
  8. 2 rows in set (0.01 sec)
  10. mysql>

使用UNION连接起来的两个查询中，第一个语句的查询列表是m1, n1，第二个查询语句的查询列表是n1, m1，我们应该注意两点：

  • 第一个查询的查询列表处的m1和第二个查询的查询列表的n1对应，第一个查询的查询列表处的n1和第二个查询的查询列表的m1对应，m1和n1虽然类型不同，但MySQL会帮助我们自动进行必要的类型转换。

  • 这几个查询语句的结果集都可以被合并到一个大的结果集中，但是这个大的结果集总是要有展示一下列名的吧，所以就规定组合查询的结果集中显示的列名将以第一个查询中的列名为准，上边的例子就采用了第一个查询中的m1, n1作为结果集的列名。

涉及不同表的组合查询

当然，如果只在同一个表中进行组合查询，貌似体现不出组合查询的强大，很多情况下组合查询还是用在涉及不同表的查询语句中的，比方说下边这两个查询：

  1. mysql> SELECT m1, n1 FROM t1 WHERE m1 < 2;
  2. +------+------+
  3. | m1   | n1   |
  4. +------+------+
  5. |    1 | a    |
  6. +------+------+
  7. 1 row in set (0.00 sec)
  9. mysql> SELECT m2, n2 FROM t2 WHERE m2 > 2;
  10. +------+------+
  11. | m2   | n2   |
  12. +------+------+
  13. |    3 | c    |
  14. |    4 | d    |
  15. +------+------+
  16. 2 rows in set (0.00 sec)
  18. mysql>

第一个查询是从t1表中查询m1, n1这两个列的数据，第二个查询是从t2表中查询m2, n2这两个列的数据。我们可以使用UNION直接将这两个查询语句拼接到一起：

  1. mysql> SELECT m1, n1 FROM t1 WHERE m1 < 2 UNION SELECT m2, n2 FROM t2 WHERE m2 > 2;
  2. +------+------+
  3. | m1   | n1   |
  4. +------+------+
  5. |    1 | a    |
  6. |    3 | c    |
  7. |    4 | d    |
  8. +------+------+
  9. 3 rows in set (0.01 sec)
  11. mysql>

包含或去除重复的行

我们看下边这两个查询：

  1. mysql> SELECT m1, n1 FROM t1;
  2. +------+------+
  3. | m1   | n1   |
  4. +------+------+
  5. |    1 | a    |
  6. |    2 | b    |
  7. |    3 | c    |
  8. +------+------+
  9. 3 rows in set (0.00 sec)
  11. mysql> SELECT m2, n2 FROM t2;
  12. +------+------+
  13. | m2   | n2   |
  14. +------+------+
  15. |    2 | b    |
  16. |    3 | c    |
  17. |    4 | d    |
  18. +------+------+
  19. 3 rows in set (0.00 sec)
  21. mysql>

很显然，t1表里有3条记录，t2表里有3条记录，我们使用UNION把它们合并起来看一下：

  1. mysql> SELECT m1, n1 FROM t1 UNION SELECT m2, n2 FROM t2;
  2. +------+------+
  3. | m1   | n1   |
  4. +------+------+
  5. |    1 | a    |
  6. |    2 | b    |
  7. |    3 | c    |
  8. |    4 | d    |
  9. +------+------+
  10. 4 rows in set (0.00 sec)
  12. mysql>

为什么合并后的结果只剩下了4条记录呢？因为使用UNION来合并多个查询的记录会默认过滤掉重复的记录。由于t1表和t2表都有(2, b)、(3, c)这两条记录，所以合并后的结果集就把他俩去重了。如果我们想要保留重复记录，可以使用UNION ALL来连接多个查询：

  1. mysql> SELECT m1, n1 FROM t1 UNION ALL SELECT m2, n2 FROM t2;
  2. +------+------+
  3. | m1   | n1   |
  4. +------+------+
  5. |    1 | a    |
  6. |    2 | b    |
  7. |    3 | c    |
  8. |    2 | b    |
  9. |    3 | c    |
  10. |    4 | d    |
  11. +------+------+
  12. 6 rows in set (0.00 sec)
  14. mysql>

组合查询中的ORDER BY和LIMIT子句

组合查询会把各个查询的结果汇总到一块，如果我们相对最终的结果集进行排序或者只保留几行的话，可以在组合查询的语句末尾加上ORDER BY和LIMIT子句，就像这样：

  1. mysql> SELECT m1, n1 FROM t1 UNION SELECT m2, n2 FROM t2 ORDER BY m1 DESC LIMIT 2;
  2. +------+------+
  3. | m1   | n1   |
  4. +------+------+
  5. |    4 | d    |
  6. |    3 | c    |
  7. +------+------+
  8. 2 rows in set (0.00 sec)
  10. mysql>

如果我们能为各个小的查询语句加上括号()那就更清晰了，就像这样：

  1. mysql> (SELECT m1, n1 FROM t1) UNION (SELECT m2, n2 FROM t2) ORDER BY m1 DESC LIMIT 2;
  2. +------+------+
  3. | m1   | n1   |
  4. +------+------+
  5. |    4 | d    |
  6. |    3 | c    |
  7. +------+------+
  8. 2 rows in set (0.01 sec)
  10. mysql>

这里需要注意的一点是，由于最后的结果集展示的列名是第一个查询中给定的列名，所以ORDER BY子句中指定的排序列也必须是第一个查询中给定的列名（别名也可以）。

这里突发一个奇想，如果我们只想单独为各个小的查询排序，而不为最终的汇总的结果集排序行不行呢？先试试：

  1. mysql> (SELECT m1, n1 FROM t1 ORDER BY m1 DESC) UNION (SELECT m2, n2 FROM t2 ORDER BY m2 DESC);
  2. +------+------+
  3. | m1   | n1   |
  4. +------+------+
  5. |    1 | a    |
  6. |    2 | b    |
  7. |    3 | c    |
  8. |    4 | d    |
  9. +------+------+
  10. 4 rows in set (0.00 sec)
  12. mysql>

从结果来看，我们为各个小查询加入的ORDER BY子句好像并没有起作用，这是因为设计MySQL的大叔规定组合查询并不保证最后汇总起来的大结果集中的顺序是按照各个小查询的结果集中的顺序排序的，也就是说我们在各个小查询中加入ORDER BY子句的作用和没加一样～ 不过如果我们只是单纯的想从各个小的查询中获取有限条排序好的记录加入最终的汇总，那是可以滴，比如这样：

  1. mysql> (SELECT m1, n1 FROM t1 ORDER BY m1 DESC LIMIT 1) UNION (SELECT m2, n2 FROM t2 ORDER BY m2 DESC LIMIT 1);
  2. +------+------+
  3. | m1   | n1   |
  4. +------+------+
  5. |    3 | c    |
  6. |    4 | d    |
  7. +------+------+
  8. 2 rows in set (0.00 sec)
  10. mysql>

如图所示，最终结果集中的(3, 'c')其实就是查询(SELECT m1, n1 FROM t1 ORDER BY m1 DESC LIMIT 1)的结果，(4, 'd')其实就是查询(SELECT m2, n2 FROM t2 ORDER BY m2 DESC LIMIT 1)的结果。