mysql子查询
where型子查询
把内层查询结果当作外层查询的比较条件
-- 不用order by 来查询最新的商品
-- 取出每个栏目下最新的产品(goods_id唯一)
select cat_id, goods_id,goods_name
from goods
where goods_id in(select max(goods_id) from goods group by cat_id);
from型子查询
--用子查询查出挂科两门及以上的同学的平均成绩
思路:
-- 先查出哪些同学挂科两门以上
select name,count(*) as gk
from stu
where score < 60 having gk >=2;
-- 以上查询结果,我们只要名字就可以了,所以再取一次名字
select name
from (select name,count(*) as gk from stu having gk >=2) as t;
-- 找出这些同学了,那么再计算他们的平均分
select name,avg(score)
from stu
where name in (
select name from (
select name,count(*) as gk from stu having gk >=2
) as t
)
group by name;
exists型子查询
把外层查询结果拿到内层,看内层的查询是否成立
--查询哪些栏目下有商品,栏目表category,商品表goods
select cat_id,cat_name
from category
where exists(select * from goods where goods.cat_id = category.cat_id);