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

This question already has answers here:

Select top 10 records for each category

（14个回答）


2年前关闭。





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

如果本文Select top 3 values from each group in a table with SQL 中提供的查询在表中没有重复项，则可以解决此问题。但是，如果有重复项，解决方案是什么？

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

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

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

最佳答案

在许多现代的DBMS（例如Postgres，Oracle，SQL-Server，DB2和许多其他DBMS）中，以下方法可以正常工作。它使用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中测试

关于sql - 如何使用SQL从表中的每个组中选择前3个值，这些值具有重复项[duplicate]，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/16720525/