mysql子查询语句from,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;