库表:
student_info:id,name,age
_info:id,field(科目),(分数)
1.查找表中年龄最大人的名字
2.学科最高分的name
3.各科平均分
4.每个人的最高总分(由高到底)另外:如何查找百万级的库表中工资最高的三个人?小弟先谢了,笔试中总是遇到
student_info:id,name,age
_info:id,field(科目),(分数)
1.查找表中年龄最大人的名字
2.学科最高分的name
3.各科平均分
4.每个人的最高总分(由高到底)另外:如何查找百万级的库表中工资最高的三个人?小弟先谢了,笔试中总是遇到
2. select name from (
select a.name,b.field,max(b.) from student_info a,_info b
where a.id = b.id
group by b.field);
3. select field,avg() from _info group by field;
4. select a.name,sum(b.) from student_info a,_info b
where a.id = b.id
group by a.name
order by sum(b.) desc;不知道有没有错
另外,感谢benny0526() 1,3,4都对了,只是2,pl/sql中说他是“不是group by表达式”
优化方案:建立索引能举个简单例子么?十分谢谢!
select name from (
select min(a.name),b.field,max(b.) from student_info a,_info b
where a.id = b.id
group by b.field);
建立索引
select min(a.name),b.field,max(b.) from student_info a,_info b
where a.id = b.id
group by b.field);
谢谢sxykje(我爱老婆):我又在pl/sql中运行了一下,说select name from(.....)中的name是无效列名
select min(a.name) as name,b.field,max(b.) from student_info a,_info b
where a.id = b.id
group by b.field);
where rownum<4
order by 工资
俺只会这样写
select min(a.name) as name,b.field,max(b.) from student_info a,_info b
where a.id = b.id
group by b.field);
这次对了,结贴