按组查询的最高值 [英] Top Values in a Query by Group

问题描述

我知道如何获得最高价值，但遇到一些非常简单的问题.

I know how to get the top values but am having trouble with something very simple.

我有一个学生桌.它具有:

I have a student table. It has:

• 名称
• numberoflaps
• 等级

我希望获得一个查询或报告，以显示每个年级单圈最多的前两个孩子.

I want the get a query or report that shows the top two kids with the most laps per grade.

推荐答案

使用MySQL:

---

MySQL没有任何排名功能，但确实允许创建变量&更新:

Using MySQL:

---

MySQL doesn't have any ranking functionality, but it does allow for variable creation & updating:

SELECT x.grade,
       x.name,
       x.numberoflaps
  FROM (SELECT s.grade,
               s.name,
               s.numberoflaps,
               CASE 
                 WHEN @grade != s.grade THEN @rownum := 1 
                 ELSE @rownum := @rownum + 1
               END AS rank,
               @grade := s.grade
          FROM STUDENTS s,
               (SELECT @rownum := 0, @grade := NULL) r
      ORDER BY s.grade, s.numberoflaps DESC) x
 WHERE x.rank <= 2
ORDER BY x.grade, x.rank

子查询中的ORDER BY很重要，否则排名将无法正确执行.

The ORDER BY in the subquery is important, otherwise the ranking won't be properly performed.

使用CTE:

WITH laps AS (
  SELECT s.grade, 
         s.name, 
         s.numberoflaps,
         ROW_NUMBER() OVER (PARTITION BY grade ORDER BY numberoflaps DESC) AS rank
    FROM STUDENTS s)
  SELECT l.grade, 
         l.name, 
         l.numberoflaps
    FROM laps l
   WHERE l.rank <= 2
ORDER BY l.grade, l.numberoflaps DESC

等效于非CTE:

  SELECT l.grade, 
         l.name, 
         l.numberoflaps
    FROM (SELECT s.grade, 
                 s.name, 
                 s.numberoflaps,
                 ROW_NUMBER() OVER (PARTITION BY grade ORDER BY numberoflaps DESC) AS rank
            FROM STUDENTS s) l
   WHERE l.rank <= 2
ORDER BY l.grade, l.numberoflaps DESC

注意事项:

Oracle在9i中获得了排名功能；对于SQL Server，是2005年.

Caveat:

Oracle gained ranking functionality in 9i; for SQL Server it was 2005.

这篇关于按组查询的最高值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！