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