返回 col 与计数的前 2 行匹配的所有行

Question

我想计算一个productID被列出多少次，然后使用它来返回productID与计数的前 2 名匹配的所有行。

所以拿这张表：

ID | productID
1  | 2 
2  | 2
3  | 3
4  | 3
5  | 4
6  | 2

查询将返回：

ID | productID
1  | 2
2  | 2
6  | 2
3  | 3
4  | 3

我不想使用LIMIT，因为我不知道会返回多少行，我需要将它们全部抓取。

我想以一种可以使用偏移量来获取下一个前 2 名的方式编写查询（所以 3-4，然后是 5-6，依此类推）

我不完全确定仅使用 SQL 是否可以做到这一点，我想出的最好的是：

SELECT ID, productID, COUNT(*)
FROM table
GROUP BY ID, productID

Answer 1

如果我

理解正确：

select t.*
from t
where t.productId in (select t2.productId
                      from t t2
                      group by productId
                      order by count(*) desc
                      limit 2
                     )
order by t.productId;

如果要在查询中使用计数（例如用于排序），请在 from 子句（"派生表"）中使用子查询：

select t.*
from t join
     (select t2.productId, count(*) as cnt
      from t t2
      group by productId
      order by count(*) desc
      limit 2
     ) tt
     on t.productId = tt.productId
order by count(*) desc, id;

最后，如果您只想要前两个的 id，那么将它们聚合到一个数组中就足够了：

select t2.productId, array_agg(id) as ids
from t t2
group by productId
order by count(*) desc
limit 2;

这将返回：

productID    ids
    2        {1,2,6}
    3        {3,4}

Answer 2

您可以找到每个产品 ID 的计数并在其上找到密集的排名，并根据计数过滤以仅获得前 2 个排名：

select
    id, productId
from (
    select
        t.*,
        dense_rank() over (order by cnt desc) rnk
    from (
        select
            t.*,
            count(*) over (partition by productId) cnt
        from your_table t
    ) t
) t where rnk <= 2
order by cnt desc, id;

Answer 3

Select * From table t
where productId In
   (Select productId from table
    group by productId
    order by count<*> desc
    limit 2)