Mysql之子查询

一、子查询使用场景

--select...

--<select 可用子查询>

--from

--where<可使用子查询>

--having<可使用子查询>

二、select中使用子查询

需求：查询各个部门中所有大于本部门平均工资的员工信息

select a.* from infor a,

(select deptno,avg(sal) as salAvg from infor group by deptno) b

where a.deptno=b.deptno and a.sal > b.salAvg;

三、where中使用子查询

1、in子查询

需求：查询各个部门中工资最高的员工信息

select * from infor where sal in

(select max(sal) from infor group by deptno);

2、where第二种子查询

需求：查询各个部门工资最高的两名员工信息

select * from infor e1 where 

(select count(1) from infor e2 where e2.deptno=e1.deptno and e2.sal>=e1.sal) < 3;

四、having中使用子查询

1、和一个比

需求：比用户tx订单数多的customer_id、city和订单数

select t1.customer_id,t1.city,count(t2.order_id) as amount from table1 t1,table2 t2

where t1.customer_id=t2.customer_id and t1.customer_id <>'tx'

group by customer_id

having (amount)>

(select count(order_id) from table2  where customer_id = 'tx' group by custer_id)

2、和多个比

需求：查询比customer_id为tx或者9you的订单数量最多的用户的id、城市和订单数量

select t1.custer_id,t1.city,count(t2.order_id) as amount from table1 t1,table2 t2

where t1.customer_id=t2.customer_id and t1.customer_id<>'tx' and t1.customer_id<>'9you'

group by customer_id having (amount) > any

(select count(order_id) from table2  where customer_id = 'tx'  or cusomer_id='9you' 

group by custer_id)