mysql--10.子查询

1      子查询/嵌套查询

查询的内容需要另一个查询的结果。

语法：select * from table where 条件 > (select *from table where 条件)

         select* from emp where ename > (select * from emp where 条件);

                  

练习：

统计部门的平均工资大于公司平均工资的部门（子查询）

         *公司的平均工资

                   selectavg(salaly) from emp;

         *部门的平均工资

                   selectd.dname,avg(salaly) from dept d,emp e where d.did = e.dno group by d.dname;

 

         selectd.dname,avg(e.salaly) as sa from dept d,emp e where d.did = e.dno group byd.dname having sa >(select avg(salaly) from emp);

 

where字句后面不能加聚合函数，用在其之前可以，having可以