mysql子查询

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);