sql - 如何使用具有重复项的 SQL 从表中的每个组中选择前 3 个值

Question

这个问题在这里已经有了答案:





Select top 10 records for each category

(14 个回答)


4年前关闭。




假设我们有一个包含两列的表，一列包含一些人的姓名，另一列包含与每个人相关的一些值。一个人可以有多个值。每个值都有一个数字类型。问题是我们要从表中为每个人选择前 3 个值。如果一个人的值少于 3 个，我们选择该人的所有值。

通过本文提供的查询Select top 3 values from each group in a table with SQL 如果表中没有重复的问题可以解决.但是如果有重复，解决办法是什么？

例如，如果对于一个名字 John，他有 5 个与他相关的值。它们是 20,7,7,7,4。我需要按每个名称的值降序返回名称/值对，如下所示:

-----------+-------+
| name     | value |
-----------+-------+
| John     |    20 |
| John     |     7 |
| John     |     7 |
-----------+-------+

即使 John 有 3 个 7，也应该只为 John 返回 3 行。

Answer 1

在许多现代 DBMS(例如 Postgres、Oracle、SQL-Server、DB2 和许多其他数据库)中，以下内容可以正常工作。它使用 CTE 和排名函数 ROW_NUMBER()这是最新 SQL 标准的一部分:

 WITH cte AS
  ( SELECT name, value,
           ROW_NUMBER() OVER (PARTITION BY name
                              ORDER BY value DESC
                             )
             AS rn
    FROM t
  )
SELECT name, value, rn
FROM cte
WHERE rn <= 3
ORDER BY name, rn ;

没有 CTE，只有 ROW_NUMBER() :

SELECT name, value, rn
FROM 
  ( SELECT name, value,
           ROW_NUMBER() OVER (PARTITION BY name
                              ORDER BY value DESC
                             )
             AS rn
    FROM t
  ) tmp 
WHERE rn <= 3
ORDER BY name, rn ; 

测试:

Postgres

Oracle

SQL-Server

在 MySQL 和其他没有排名功能的 DBMS 中，必须使用派生表、相关子查询或带有 GROUP BY 的自连接。 .
(tid)假定为表的主键:

SELECT t.tid, t.name, t.value,              -- self join and GROUP BY
       COUNT(*) AS rn
FROM t
  JOIN t AS t2
    ON  t2.name = t.name
    AND ( t2.value > t.value
        OR  t2.value = t.value
        AND t2.tid <= t.tid
        )
GROUP BY t.tid, t.name, t.value
HAVING COUNT(*) <= 3
ORDER BY name, rn ;


SELECT t.tid, t.name, t.value, rn
FROM
  ( SELECT t.tid, t.name, t.value,
           ( SELECT COUNT(*)                -- inline, correlated subquery
             FROM t AS t2
             WHERE t2.name = t.name
              AND ( t2.value > t.value
                 OR  t2.value = t.value
                 AND t2.tid <= t.tid
                  )
           ) AS rn
    FROM t
  ) AS t
WHERE rn <= 3
ORDER BY name, rn ;

测试于 MySQL