Max与Equality查询

Question

假设我有一个如下表，所以我的目标是为匹配的游戏找到最大点数，而不是ID，但是如果积分是等价的，比如1-x-5和3-x-5，我想从数据集中忽略它。谁能帮我谢谢你！

id game point
1    x   5
1    z   4
2    y   6
3    x   5
3    y   5
3    z   8

所以只给我

id game point
2   y    6
3   z    8

我尝试了一些下面的编码，但是数据不正确。

SELECT T1.ID, T1.Game, T1.Point
FROM TableName As T1
INNER JOIN (SELECT T2.ID, Min(T2.Point) As MinOfPoint
FROM TableName As T2
GROUP BY T2.ID) As SQ
ON T1.ID=SQ.ID And T1.Point=SQ.MinOfPoint 

Answer 1

这个怎么样？它有点脏，但我认为它能起作用。

SELECT T3.id, T3.game, T3.point
FROM TableName AS T3
INNER JOIN
    (SELECT T1.game, MAX(T1.Mpoint) AS RPoint
    FROM
        (SELECT game, MAX(point) AS MPoint
        FROM TableName
        GROUP BY game, point
        HAVING COUNT(point) = 1) AS T1
    GROUP BY T1.game) AS T2
ON T2.game = T3.game
   AND T2.RPoint = T3.point;

Answer 2

这应该能做你想要做的事情，如果我能正确理解你想要完成的事情的话。

select T1.Game, T1.Point
from (select T1.Game, Max(T1.Point) as MaxPoint from T1 Group By T1.Game) as GroupData inner join T1 on GroupData.Game = T1.Game AND GroupData.MaxPoint = T1.Point 
group by T1.game, T1.point 
having Count(T1.id) = 1;