从列表中为每个帐户id提取一行，第2部分

Question

不确定如何询问SO的后续问题，但这是参考前面的一个问题：Fetch one row per account id from list

我使用的查询是：

SELECT *
FROM scores s1
WHERE accountid NOT IN (SELECT accountid FROM scores s2 WHERE s1.score < s2.score)
ORDER BY score DESC

这将选择最高的分数，并将结果限制为每个帐户and的一行；它们的最高分数。

最后一个障碍是，此查询将为多次出现最高分的帐户is返回多个行。因此，如果accountid 17的分数为40、75、30、75，则查询将返回分数为75的两行。

是否有人可以修改此查询(或提供更好的查询)来修复此情况，并真正将其限制为每个帐户id只有一行？

再次感谢！

Answer 1

如果您只对帐户If和分数感兴趣，那么可以使用上面Paul给出的简单的GROUP BY查询。

SELECT accountid, MAX(score) 
FROM scores 
GROUP BY accountid;

如果您需要来自分数表的其他属性，则可以使用如下查询从该行获取其他属性：

SELECT s1.*
FROM scores AS s1
  LEFT OUTER JOIN scores AS s2 ON (s1.accountid = s2.accountid 
    AND s1.score < s2.score)
WHERE s2.accountid IS NULL;

但这仍然会产生多个行，在您的示例中，给定的accountid有两个分数与其最大值匹配。要进一步将结果集减少到单个行，例如具有最新gamedate的行，请尝试执行以下操作：

SELECT s1.*
FROM scores AS s1
  LEFT OUTER JOIN scores AS s2 ON (s1.accountid = s2.accountid 
    AND s1.score < s2.score)
  LEFT OUTER JOIN scores AS s3 ON (s1.accountid = s3.accountid 
    AND s1.score = s3.score AND s1.gamedate < s3.gamedate) 
WHERE s2.accountid IS NULL
    AND s3.accountid IS NULL;

Answer 2

select accountid, max(score) from scores group by accountid;

Answer 3

如果您的RDBMS支持它们，那么分析函数将是一种很好的方法，特别是当您需要行的所有列时。

select ...
from   (
   select accountid,
          score,
          ...
          row_number() over 
               (partition by accountid
                    order by score desc) score_rank
   from   scores)
where score_rank = 1;

在您描述的情况下，返回的行是不确定的，但您可以轻松地修改分析函数，例如，通过对(score desc，test_date desc)进行排序，以获得两个匹配的较高分数中较新的一个。

其他基于排名的分析函数也会达到类似的目的。

如果你不介意重复，那么下面的方法可能比你当前的方法更有效：

select ...
from   (
   select accountid,
          score,
          ...
          max(score) over (partition by accountid) max_score
   from   scores)
where score = max_score;