一张学生表student,有班级号classid,姓名name,分数grade
1)每一个班级的平均分数
2)查高于一个班级平均数所有班级号classid,姓名name,分数grade用户登录表T(id,name),登录时间表A(id,name,time),购物表G(id,time,shopid)
1)最新登录的用户id,name,time
2)最近购物的time,id,name
1)每一个班级的平均分数
2)查高于一个班级平均数所有班级号classid,姓名name,分数grade用户登录表T(id,name),登录时间表A(id,name,time),购物表G(id,time,shopid)
1)最新登录的用户id,name,time
2)最近购物的time,id,name
select classid,avg(grade) from student group by classid;
--查高于一个班级平均数所有班级号classid,姓名name,分数grade
--没太清楚
--1、高于任意一个班的平均分数?
select * from student
where grade>
(select min(avg(grade)) from student group by classid);
--2、高于每一个班级的平均分数?
select * from student
where grade>
(select max(avg(grade)) from student group by classid);--最新登录的用户id,name,time
select * from A where time=(select max(time) from A);
--最近购物的time,id,name
select * from G where time=(select max(time) from G);
--最近购物
select G.time,G.id,T.name from G left join T on(G.shopid=T.id)
where G.time=(select max(G.time) from G);
--只高于一个班级的平均数
select * from student
where classid=(
select clasid from
(select classid,avg(grade),row_number()over(order by avg(grade) asc) rn from student group by classid) where rn=2);
--有没说请的
我猜的
1 select classid,avg(grade) avg_grade from student group by classid
2
select * from student a where grade>
(select avg(grade) from student b where a.classid=b.classid group by classid)3
select id,name,max(time)
from A
group by id,name4
select t.id,t.name,max(g.time)
from t,g
where t.id=g.id
group by t.id,t.name
1)每一个班级的平均分数select classid,avg(grade) from student group by classid;
2)查高于一个班级平均数所有班级号classid,姓名name,分数gradeselect classid,name,grade
from
(select classid,name,grade,avg(grade) over (partition by classid) avg_grade from student)
where grade > avg_grade
用户登录表T(id,name),登录时间表A(id,name,time),购物表G(id,time,shopid)
1)最新登录的用户id,name,timeselect id,name,time from (select id,time,name,row_number() over (order by time desc) rn from A) where rn = 1
2)最近购物的time,id,nameselect shop.time,shop.id,T.name
from T, (select id,time from (select id,time,row_number() over (order by time desc) rn from G) where rn = 1) shop
where T.id = shop.id
2.select * from student a where grade>
(select avg(grade) from student b where a.classid=b.classid group by classid)
3.select * from A where time=(select max(time) from A);
4.select shop.time,shop.id,T.name
from T, (select id,time from (select id,time,row_number() over (order by time desc) rn from G) where rn = 1) shop
where T.id = shop.id
(select avg(grade) from student b where a.classid=b.classid group by classid)
此查询效率很低吧
select classid,[name],avg(grade) from student group by classid,[name]
2、--查询高于一个班级平均数所有班级号classid,name,grade
select classid,[name],grade from student
where grade>(select classid,[name],min(grade) from student group by classid,[name]
3、--最新登录的用户id,name,time
select id,[name],max(time) from T group by id,[name]
4、--最近购物的time,id,name
select * from G where time=(select id,shopid,max(time) from G group by id,shopid)