用WHERE替换WHERE

Question

SELECT team, sum(maths)
FROM marks
GROUP BY team
HAVING sum(maths) = (
    SELECT max(sum(maths))
    FROM marks
    GROUP BY team
)

有三个团队A，B，C，其中有一些没有成员。

首先，使用SELECT max(sum(maths)) FROM marks GROUP BY team，我得到了数学的最大和。

现在，使用整个查询，我试图显示数学成绩最高的小组。

如何重写查询，以便用WHERE替换HAVING。

Answer 1

请尝试：

select * from (
  select team, v maxmarks, row_number() over (order by v desc) RNum from(
    SELECT team, sum(maths) v
    FROM marks 
    group by team 
  )x
)xx where RNum=1;

Answer 2

可以将there替换为WHERE，但这样做没有任何好处。

select team, sum(maths) from
(
   SELECT team, sum(maths) as total
   FROM marks
   GROUP BY team
) t1
where t1.total = ( select max(t1.total) from t1 )

Answer 3

如果对结果进行排序，则可以使用ROWNUM pseudocolumn将它们包装在外部SELECT中，从而将其限制为第一行

SELECT *
FROM (
    SELECT team, sum(maths)
    FROM marks
    GROUP BY team
    ORDER BY sum(maths) DESC
)
WHERE ROWNUM = 1;

或者，您可以使用ROW_NUMBER analytic function，再次包装在外部select中：

SELECT *
FROM (
    SELECT team, sum(maths),
        ROW_NUMBER() OVER (PARTITION BY team ORDER BY sum(maths) DESC) AS rn
    FROM marks
    GROUP BY team
)
WHERE rn = 1;