create table studentInfo(
id number(8) primary key,
name varchar2(20) not null,
ObjectName varchar2(20) not null,
fenshu varchar2(2) not null
);
insert into studentInfo values(1,'张三','数学',70);
insert into studentInfo values(2,'张三','语文',80);
insert into studentInfo values(3,'张三','英语',90);
insert into studentInfo values(4,'王二','数学',80);
insert into studentInfo values(5,'王二','语文',70);
insert into studentInfo values(6,'李四','数学',90);
insert into studentInfo values(7,'李四','语文',90);select distinct name,max(fenshu) from studentInfo group by name如果我要得到学员中每一科成绩最高的学员,怎么写语句
name ObjectName fenshu
* * *
求大侠指教
select *
from (SELECT tt.*,
Row_Number() OVER(partition by tt.ObjectName ORDER BY fenshu desc) rn
from studentInfo tt)
where rn = 1
(select objectName,max(fenshu) as maxfenshu from studentInfo group by objectName) t, studentInfo t2
where t.objectName=t2.objectname and t.maxfenshu=t2.fenshu;
from studentinfo st
where fenshu = (select max(fenshu) from studentinfo s where st.objectname=s.objectname);
create table studentInfo(
id number(8) primary key,
name varchar2(20) not null,
ObjectName varchar2(20) not null,
fenshu varchar2(2) not null
);
insert into studentInfo values(1,'张三','数学',70);
insert into studentInfo values(2,'张三','语文',80);
insert into studentInfo values(3,'张三','英语',90);
insert into studentInfo values(4,'王二','数学',80);
insert into studentInfo values(5,'王二','语文',70);
insert into studentInfo values(6,'李四','数学',90);
insert into studentInfo values(7,'李四','语文',90);
select name,ObjectName, fenshu
from
(
select name,ObjectName,fenshu,
row_number() over(partition by ObjectName order by fenshu desc) rn
from studentInfo
)
where rn = 1 name ObjectName fenshu
---------------------------------
1 李四 数学 90
2 张三 英语 90
3 李四 语文 90
SELECT a.NAME,a.Objectname,a.fenshu FROM studentinfo a ,
(SELECT Objectname,MAX(fenshu) fenshu from studentinfo t
GROUP BY Objectname ) b
WHERE a.objectname = b.objectname AND a.fenshu = b.fenshu
select t1.objectname, t1.name, t1.fenshu
from studentInfo t1
where exists (select 1
from studentInfo t2
where t2.objectname=t1.objectname
group by t2.objectname
having max(t2.fenshu)=t1.fenshu );
--为了防止并列第一出现,使用rank()函数
select *
from (SELECT tt.*,
rank() OVER(partition by tt.ObjectName ORDER BY fenshu desc) rn
from studentInfo tt)
where rn = 1
name objectname mxfenshu
李四 数学 90
李四 语文 90
张三 英语 90思路:
1:先按照objectname分组查询每科的最好高分
select objectname,max(fenshu) mxfenshu from studentinfo group by objectname
2.把查询的每科最高分当做一张表和原表做连接查询。