mysql--（入门语句18-from型子查询）

#from型子查询

把内层的查询结果当临时表，供外层sql再次查询

#所有商品

#所有商品
1)where型子查询，查出每个栏目下最新的商品：
  select goods_id,cat_id,goods_name from goods order by cat_id asc,goods_id desc;
  select max(goods_id) from goods group by cat_id;
  select goods_id,cat_id,goods_name from goods where goods_id in (select max(goods_id) from goods group by cat_id);
2）不用where型子查询，用from型子查询：(查询出每个栏目下最新的商品)
  select goods_id,cat_id,goods_name from goods order by cat_id asc,goods_id desc;//相当于建立一个临时表
  select * from  (select goods_id,cat_id,goods_name from goods order by cat_id asc,goods_id desc) as tmp group by cat_id;

#如何用子查询查出挂科两门及以上同学的平均分，where，from型都可以

#如何用子查询查出挂科两门及以上同学的平均分，where，from型都可以
select *from stu;
先把挂科2门及以上的同学找出来
1）不及格的同学：
   select name,count(*) from stu where score <60 group by name;
2)然后将挂科两门以上的同学找出来：
   select name,count(*) as gk from stu where score <60 group by name having gk>=2;
3)将人员抽离出来：
   select name from (select name,count(*) as gk from stu where score <60 group by name having gk>=2) as tmp;
4）求平均分：
   select name,acg(score) from score stu where name in(select name from (select name,count(*) as gk from stu where score <60 group by name having gk>=2) as tmp) group by name;