mysql组合查询(union和union all)

mysql组合查询(union和union all)

组合查询

组合查询: 执行多条select语句，并将结果作为单个查询结果返回，这些组合查询通常称为并(union)或复合查询(compound query)

场景

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

union规则

• union必须由两条以上的select语句组成，语句之间使用union分隔
• union中的查询必须 包含相同的列、表达式或聚合函数(不过各个列不需要以相同的次序列出)
• 列的数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型(例如不同的数值类型或不同的日期类型)

SELECT * FROM `fruits` ff WHERE ff.f_price < 10
UNION
SELECT * FROM fruits fv WHERE fv.s_id in(102,105)

//结果:  
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| a1   |  101 | apple   | 5.2     |
| a2   |  103 | arpicot | 2.2     |
| b2   |  104 | berry   | 7.6     |
| b5   |  107 | xxxx    | 3.6     |
| bs2  |  105 | melon   | 8.2     |
| c0   |  101 | cherry  | 3.2     |
| l2   |  104 | lemon   | 6.4     |
| m2   |  105 | xbabay  | 2.6     |
| o2   |  103 | coconut | 9.2     |
| t2   |  102 | grape   | 5.3     |
| t4   |  107 | xbaba   | 3.6     |
| bs1  |  102 | orange  | 11.2    |
| m3   |  105 | xxtt    | 11.6    |
| t1   |  102 | banana  | 10.3    |
+------+------+---------+---------+

union与union all的不同

union从查询结果中自动去除了重复行，这是union的默认行为。事实上，如果想要返回所有的匹配行，可以使用union all来代替union

对组合查询结果排序

SELECT 语句的输出用 ORDER BY 子句排序。在用 UNION 组合查询时，只
能使用一条 ORDER BY 子句，它必须出现在最后一条 SELECT 语句之后。对
于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一
部分的情况，因此不允许使用多条 ORDER BY 子句

mysql> 
SELECT * FROM `fruits` ff WHERE ff.f_price < 10
UNION
SELECT * FROM fruits fv WHERE fv.s_id in(102,105)
ORDER BY f_price;

//结果: 
+------+------+---------+---------+
| f_id | s_id | f_name  | f_price |
+------+------+---------+---------+
| a2   |  103 | arpicot | 2.2     |
| m2   |  105 | xbabay  | 2.6     |
| c0   |  101 | cherry  | 3.2     |
| b5   |  107 | xxxx    | 3.6     |
| t4   |  107 | xbaba   | 3.6     |
| a1   |  101 | apple   | 5.2     |
| t2   |  102 | grape   | 5.3     |
| l2   |  104 | lemon   | 6.4     |
| b2   |  104 | berry   | 7.6     |
| bs2  |  105 | melon   | 8.2     |
| o2   |  103 | coconut | 9.2     |
| t1   |  102 | banana  | 10.3    |
| bs1  |  102 | orange  | 11.2    |
| m3   |  105 | xxtt    | 11.6    |
+------+------+---------+---------+
14 rows in set

虽然 ORDER BY 子句似乎只是最后一条 SELECT 语句的组成部分，但实际上MySQL将用它来排序所有 SELECT 语句返回的所有结果。