sql 分组后查询最大所有列信息

CREATE TABLE students 
    (course varchar(10),
        stu_name varchar(10),
        city    varchar(10),
        score   int
    )

insert into students values('数学','Jack','Tianjin',80);
insert into students values('数学','Jordan','Tianjin',80);
insert into students values('数学','James','Beijing',55);
insert into students values('英语','Jack','Tianjin',90);
insert into students values('英语','Jordan','Tianjin',60);
insert into students values('英语','James','Beijing',100);
insert into students values('语文','Jack','Tianjin',60);
insert into students values('语文','Tom','Tianjin',77);
insert into students values('语文','Jordan','Tianjin',68);

DELETE from students

SELECT * from students
--最大是多条的时候取一条
select  *
    from (
        select course,stu_name,city,score,ROW_NUMBER() over(partition by course order by score desc) as rn
        from students
        ) a
    where a.rn <=1;  
--最大是多条的时候都取
select 
--a.course,a.score,b.stu_name,b.city,b.course course1,b.score score1 
b.*
from 
(
SELECT course,max(score) score from students GROUP BY course
) a
LEFT JOIN
students b on
a.course=b.course and a.score=b.score；

　　

转载于:https://www.cnblogs.com/liuqiyun/p/8087211.html