在Oracle SQL上仅排除一个MIN值 [英] Excluding only one MIN value on Oracle SQL

问题描述

我试图选择一列中的所有值(最低值)(GameScore)，但是当此最低值中有两个时，我的代码将这两个值都排除在外(我知道为什么这样做，我只是不知道具体如何对其进行更正并包括两个最低值之一).

I am trying to select all but the lowest value in a column (GameScore), but when there are two of this lowest value, my code excludes both (I know why it does this, I just don't know exactly how to correct it and include one of the two lowest values).

代码看起来像这样:

SELECT Id, SUM(Score) / COUNT(Score) AS Score
FROM 
    (SELECT Id, Score
    FROM GameScore
    WHERE Game_No = 1
      AND Score NOT IN 
        (SELECT MIN(Score)
        FROM GameScore
        WHERE Game_No = 1
        GROUP BY Id))
 GROUP BY Id

因此，如果我从5个值中进行绘制，但是由于底部的两个相同，那么其中一行仅获得3分，我该如何包含第4个?谢谢.

So if I am drawing from 5 values, but one of the rows only pulls 3 scores because the bottom two are the same, how do I include the 4th? Thanks.

推荐答案

为此，您必须以某种方式将它们分开；您当前的问题是，最低的2个分数相同，因此对两个值进行的任何(不等式)运算都会对另一个值进行相同的处理.

In order to do this you have to separate them up somehow; your current issue is that the 2 lowest scores are the same so any (in)equality operation performed on either values treats the other one identically.

您可以使用类似分析查询之类的方法 以唯一标识行:

You could use something like the analytic query ROW_NUMBER() to uniquely identify rows:

select id, sum(score) / count(score) as score
  from ( select id, score, row_number() over (order by score) as score_rank
           from gamescore
          where gameno = 1
                )
 where score_rank <> 1
 group by id

ROW_NUMBER():

以order_by_clause中指定的行的有序序列(从1开始)为应用该行的每一行(分区中的每一行或查询返回的每一行)分配一个唯一的编号.

assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1.

由于ORDER BY子句按升序排列在SCORE上，因此最低分数之一将被删除.除非您将其他平局条件添加到ORDER BY中，否则这将是一个随机值.

As the ORDER BY clause is on SCORE in ascending order one of the lowest score will be removed. This will be a random value unless you add other tie-breaker conditions to the ORDER BY.

这篇关于在Oracle SQL上仅排除一个MIN值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！