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

发布时间：2020-12-12 06:51:31 所属栏目：MsSql教程来源：网络整理

导读：参见英文答案 Select top 10 records for each category14个假设我们有一个包含两列的表,一列包含一些人的名字,另一列包含与每个人相关的一些值.一个人可以拥有多个价值.每个值都有一个数字类型.问题是我们想从表格中为每个人选择前3个值.如果一个人的值少于

参见英文答案 > Select top 10 records for each category14个
假设我们有一个包含两列的表,一列包含一些人的名字,另一列包含与每个人相关的一些值.一个人可以拥有多个价值.每个值都有一个数字类型.问题是我们想从表格中为每个人选择前3个值.如果一个人的值少于3,我们会选择该人的所有值.

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

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

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

John应该只返回3行,即使John有3个7.

解决方法

在许多现代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,rn
FROM cte
WHERE rn <= 3
ORDER BY name,rn ;

没有CTE,只有ROW_NUMBER()：

SELECT name,rn
FROM 
  ( SELECT name,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.value
HAVING COUNT(*) <= 3
ORDER BY name,rn ;


SELECT t.tid,rn
FROM
  ( SELECT t.tid,( 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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!