基本sql语句--子查询
时间：2021-07-01 10:21:17
帮助过：2人阅读
子查询有三种：where型子查询，from型子查询，exist子查询。
where型子查询
where子查询：指把内层的查询结果当成外层查询的比较条件。
例句：查询出最新的商品：select good_id,good_name from good where good_id = (select max(good_id) from good);--此句为子查询
select good_id,good_name from good order by good_id DESC limit 1;
from型子查询
from子查询：把内层的查询结果当成临时表，供外层sql再次查询。
例：查询每个栏目下最新的商品：
select good_id,cat_id,good_name from good where good_id in (select max(good_id) from good group by cat_id);---典型的where型子查询
select * from (select good_id,cat_id,good_name from good order by cat_id asc,good_id desc) as tmp group by cat_id;--from型子查询
exists型子查询
exists子查询：把外层的查询结果，拿到内层，看内层的查询是否成立。
典型示例：select cat_id,cat_name from category where exists (select * from good where good.cat_id = category.cat_id);
该子查询现在比较少用，已被join取代。select cat_id,cat_name from category join good ON category.cat_id = good.cat_id;
基本sql语句--子查询
标签：
