求多种方法
一个班级分有几个小组，考试成绩记录在数据库中的某个表tScores中，表中字段有学生[StudentName],小组名[GroupName],成绩[Score],现要统计每个小组中分数最高（如果有多个就都显示）的学生、所在小组、所得成绩，请写出你认为最简单sql语句。 方法多多，谁的效率最快？ sql
[解决办法]
select *
from tscores a
where exists (select 1 from (select max(score)score,groupname,StudentName from tscores group by groupname,StudentName)b where a.groupname=b.groupname and a.StudentName=b.StudentName
and a.score=b.score
)没测，你试试
[解决办法]
select StudentName '学生',
GroupName '所在小组',
Score '所得成绩'
from
(select StudentName,GroupName,Score,
rank() over(partition by GroupName order by Score desc) 'rn'
from tScores) t
where rn=1
select tabAAA.[GroupName],tabAAA.组最高分,tScores.[StudentName]
(
select distinct [GroupName],(select max([Score]) from tScores where [GroupName]=tbA.[GroupName]) as 组最高分 from tScores tbA)tabAAA
left join tScores on tabAAA.[GroupName]=tScores.[GroupName] and tabAAA.[Score]=tScores.[Score]
;with cte(StudentName,GroupName,Score) as
(
select '张一','一组','90' union all
select '张二','一组','58' union all
select '张三','一组','90' union all
select '李一','二组','78' union all
select '李二','二组','97' union all
select '李三','二组','45' union all
select '王一','三组','78' union all
select '王二','三组','98' union all
select '王三','三组','33'
)
select StudentName,GroupName,Score from (
select DENSE_RANK() over(partition by GroupName order by Score desc)id,* from cte
)a where id=1
order by GroupName desc
--------------------------
StudentName GroupName Score
----------- --------- -----
张三 一组 90
张一 一组 90
王二 三组 98
李二 二组 97
(4 行受影响)
create table cte(
StudentName varchar(10),GroupName varchar(10),Score int
)
insert into cte
select '张一','一组','90' union all
select '张二','一组','58' union all
select '张三','一组','90' union all
select '李一','二组','78' union all
select '李二','二组','97' union all
select '李三','二组','45' union all
select '王一','三组','78' union all
select '王二','三组','98' union all
select '王三','三组','33'
select c1.*
from cte c1
inner join
(
select GroupName,
MAX(Score) as score
from cte
group by GroupName
)c2
on c2.GroupName = c1.GroupName
and c2.score = c1.Score
/*
StudentNameGroupNameScore
张一 一组 90
张三 一组 90
王二 三组 98
李二 二组 97
*/
