限制最大和分组依据返回太多行 [英] Limit Max and Group by returned too many rows

问题描述

我正在使用SQL Server数据库.

I'm using a SQL Server database.

鉴于以下查询，我试图获得每个班的最高分. Scores表有两个类的50行，所以我总共要2行.但是，因为我有Scores.Id，所以它返回Scores的每一行，因为Scores.Id是唯一的.当然，简单的解决方案是删除Scores.Id列，但我需要知道Scores.Id才能进行其他查找.

Given the following query, I'm trying to get the highest score for each class. The Scores table has 50 rows for two classes so I want a total of 2 rows. But, because I have the Scores.Id, it returns each row for the Scores since Scores.Id is unique. Of course the simple solution is to remove the Scores.Id column except I need to know the Scores.Id to do additional lookup.

SELECT 
    Class_Id, Scores.Id, MAX(Scores.ClassScore) AS Score
FROM  
    Classes 
INNER JOIN
    Scores ON Classes.Id = Scores.Class_Id                
GROUP BY 
    Scores.Class_Id, Scores.Id

推荐答案

class_id的最高得分很简单

The max score by class_id is simply

select class_id, max(classScore) score from scores
group by class_id

如果您随后需要知道分数表中的哪些行具有最高分数，则可以进行联接.如果每个班级的最高分数相等于一个以上，您可能会得到两行以上.

If you then need to know which rows in the scores table had the max score you can do a join. You may get more than two rows if there's more than one equal max score per class.

select id, class_id, classscore from
scores s
inner join
(
    select class_id, max(classScore) score from scores
    group by class_id
)
t
on t.class_id = s.class_id and t.score = s.classScore

或者我可以使用cte

Or I might use a cte

with maxScores as
(
    select class_id, max(classScore) score from scores
    group by class_id
)
select id, class_id, classscore from
scores s
on maxScores.class_id = s.class_id and maxScores.score = s.classScore

这篇关于限制最大和分组依据返回太多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！