获取SQL查询中每个不同记录的最大平均值 [英] Get max average for each distinct record in a SQL Query

问题描述

我有一些表格，其中包含有关本赛季在联赛期间在保龄球馆打球的球员和比赛的数据。此特定查询的用途是对今年男性和女性的X平均值进行排序。我把所有这些都记下来了，但是在某些情况下，当某些球员参加多个联赛并且在前十名中拥有超过一个平均值的时候，我仍然遇到问题。

很明显，我只想列出给定球员的最佳平均数，因此，如果球员A在ABC联赛中拥有200的最佳平均水平，而在DEF联盟中拥有198的第二最佳平均水平，我只希望列出200。 / p>

这是我想更改的查询的简化版本，因为现在我必须手动删除重复项，或者必须用另一种语言编写分类器，但我宁愿在纯SQL中执行此操作。 （在此示例中，我仅从查询中删除了不相关的信息）：

  SELECT playerId，ROUND（AVG（score），2）平均值，赛季，联赛名称，COUNT（得分）NumGames FROM Scores 
 WHERE season ='2011-2012'AND score> -1 
按赛季，玩家ID，联赛名称分组
按平均DESC限制订购0.30 
  

基本上，得分表包含每个单独的游戏，一个玩家编号，玩游戏的季节以及联赛名称（以及其他不需要的列）

WHERE 是为了确保比赛在本赛季进行且得分为正（-1代表没有人时）。我将所有赛季，球员ID和LeagueName分组，因此我得到了每个球员的平均每个联盟，而不是不同联赛中所有比赛的平均值。

DISTINCT 关键字，但这不起作用，因为我不能仅在单个列中使用 DISTINCT 。我还尝试了其他方法，但是没有一个方法甚至无法工作，所以我想知道是否有可能这样做，还是我不得不使用另一种语言对结果集进行排序并删除重复项？ p>

解决方案

您可以在子查询中计算每个联赛每个球员的平均水平：

 从（
 select playerId 
，avg（score）as score 
中选择玩家编号
，max（league_avg.score）
来自得分
，其中季节='2011-2012'
且得分> -1 
组，由
 playerId 
，LeagueName 
）作为League_avg 
组，由
玩家编号
  

I have some tables that contain data about players and the games they have bowled this season in a bowling center during their leagues. What this particular query is used for is to sort the top X averages this year for men and for women. I have all of this down, but I still have a problem in some particular case when some players play in multiple leagues and have more than one of their averages in the top X.

Obviously, I only want to list the best average for a given player, so if Player A has the best average with 200 in League ABC and also the second best average with 198 in League DEF, I only want the 200 listed.

Here's a simplified version of the query that I would like to change, because right now I have to remove the duplicates manually or I would have to write a sorter in another language, but I'd rather do it in pure SQL. (I only removed irrelevant information from the query for this example):

SELECT playerId, ROUND(AVG(score),2)Average, season, leagueName, COUNT(score)NumGames FROM Scores
WHERE season = '2011-2012' AND score > -1
GROUP BY season, playerID, leagueName
ORDER BY Average DESC LIMIT 0,30

Basically, the Scores table contains each individual game, a playerId, the season in which the game was played and the leagueName (and other columns that are not required in this example).

The WHERE is to make sure the game was played this season and that the score is positive (-1 is for when people are absent). I group everything by season, playerID and leagueName so I get an average PER LEAGUE for each player instead of an average of all the games played in different leagues.

I tried using the DISTINCT keyword, but that doesn't work because I can't use DISTINCT for only a single column. I also tried other things but none of them came even close to working, so I'm wondering if it's even possible to do this or if I'll have to resort to using another language to sort this result set and removing duplicates?

解决方案

You could calculate the average per player per league in a subquery:

select  playerId
,       max(league_avg.score)
from    (
        select  playerId
        ,       avg(score) as score
        from    Scores
        where   season = '2011-2012' 
                and score > -1
        group by
                playerId
        ,       leagueName
        ) as league_avg
group by
        playerId

这篇关于获取SQL查询中每个不同记录的最大平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！