选择具有重复项的列中的前 5 个 [英] Selecting the top 5 in a column with duplicates

问题描述

我编写了一个查询，提供了一个数据列表，我需要选择前 5 条数据.例如

I have written a query that gives me a list of data and I need to select the top 5 pieces of data. For example

Num  Name
5    a
4    b
4    c
2    d
1    e
1    f
1    g
0    h

但是，如果我只是使用 LIMIT 5，这会遗漏名称为 f 和 g 的数据点.我如何能够从 a-h 中进行选择.这只是一个示例数据块.我的实际数据包含更多行，因此我可以简单地排除底行.编辑抱歉，当我说前 5 名时，我的意思是前 5 名 Num 条目.所以 5, 4 ,2 ,1,0 but 1 有重复项，所以我想选择所有这些重复项

However if I simply use LIMIT 5, this leaves out data points with name f and g. How would I be able to select from a-h. This is just an example data piece. My actual data contains a lot more rows so I can simply just exclude the bottom row. EDIT sorry when i said top 5 i meant top 5 Num entries. so 5, 4 ,2 ,1,0 but 1 has duplicates so I wanted to select all of these duplicates

推荐答案

我认为您需要这样的查询:

I think you need a query like this:

SELECT *
FROM (
    SELECT t1.Num, t1.Name, COUNT(DISTINCT t2.Num) AS seq
    FROM yourTable t1
        LEFT JOIN
        yourTable t2
        ON t1.Num <= t2.Num
    GROUP BY t1.Num, t1.Name) dt
WHERE (seq <= 5);

[SQL 小提琴演示]

这篇关于选择具有重复项的列中的前 5 个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！