mysql子查詢

標籤：

1、where型子查詢

（把內層查詢結果當作外層查詢的比較條件）

#不用order by 來查詢最新的商品

select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);

#取出每個欄目下最新的產品(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); 

2、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;

3、exists型子查詢

（把外層查詢結果拿到內層，看內層的查詢是否成立）
#查詢哪些欄目下有商品，欄目表category,商品表goods

select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);

mysql子查詢