MySQL(16):Select-union(联合查询)
1. Select-union(联合查询)
union用于把来自许多SELECT语句的结果组合到一个结果集合中。
用法:
SELECT ...UNION [ALL | DISTINCT]SELECT ...[UNION [ALL | DISTINCT]SELECT ...]
2. 引入Select-union(联合查询)的案例:
(1)首先查询出所有teacher_class列中所有的记录,如下:![](https://images2015.cnblogs.com/blog/757858/201510/757858-20151016165639085-216349383.jpg)
(2)按照字段t_name和days,查询出所有带过php0115班的老师,如下:
![](https://images2015.cnblogs.com/blog/757858/201510/757858-20151016165723960-633829848.jpg)
(3)在上面(2)查询出来的结果基础上,按照days进行排序检索,如下:
![](https://images2015.cnblogs.com/blog/757858/201510/757858-20151016165814804-2077795199.jpg)
(4)在(3)查询结果的基础上,检索出days最大的记录,如下:
![](https://images2015.cnblogs.com/blog/757858/201510/757858-20151016165911632-1162524655.jpg)
(5)如果我们想查询出php0228班的授课天数(days最大)老师记录,就需要上面查询命令的班级号就行了,如下:
select t_name,days from teacher_class where c_name='php0228' order by days desc limit 1;
如此一来,如果数据库内容很多,我们可能要重复很多这样的业务逻辑,该怎么优化呢?
3. Select-union(联合查询)的案例:
Select-union(联合查询):把多条select语句的结果,合并在一起。
使用uinion关键字,联合两个select语句即可。
现在说明我们的需求:获得2个班代课最后的老师。
语句1:select t_name,days from teacher_class where c_name='php0115' order by days desc limit 1;
语句2:select t_name,days from teacher_class where c_name='php0228' order by days desc limit 1;
使用union关键字联合上面两个select语句即可。
(1)语句1 和 语句2,查询结果如下:
![](https://images2015.cnblogs.com/blog/757858/201510/757858-20151016170057116-624601799.jpg)
(2)使用union关键字联合上面两个select语句,如下:
(select t_name,days from teacher_class where c_name='php0115' order by days desc limit 1)union(select t_name,days from teacher_class where c_name='php0228' order by days desc limit 1
);
![](https://images2015.cnblogs.com/blog/757858/201510/757858-20151016170144569-723230949.jpg)