mysql 子查询
where型子查询:
将内部查询的结果作为外部查询的条件
典型题:查询最大商品,最新商品
取出本网站最新的(goods_id 最大)的一条商品
select goods_id,cat_id ,goods_name from goods order by goods_id desc limit 1;
查出本网站最新的(goods_id最大)的一条商品(不用排序)
select goods_id,goods_name from goods where goods_id=(select max(goods_id) from goods);
查出每个栏目下,goods_id 最大的商品
select goods_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id);
from 子查询
将内层sql的查询结果当成一张临时表,进行外部查询
查询每个栏目下,goods_id最大的商品
select *from(select goods_id,goods_name,cat_id from goods order by goods_id desc) as temp group by cat_id;
exist 型子查询
是指把外层sql的结果拿到内层sql去测试,
如果内层的sql成立,则该行取出
查出有商品的栏目(取栏目,且只取栏目下有商品的)
select cat_id,cat_name,from category
where wxists (select* from goods where goods.cat_id=category.cat_id);