mysql - 使用单个查询获取列中每个值的顶级类别

Question

假设我们有一个如下表：

country    product     order
France     cheese      1
UK         cheese      2
Germany    sausage     3
UK         beer        4
UK         beer        5
France     wine        6
France     pork        7
France     wine        8
Germany    pork        9
UK         butter      10
UK         cheese      11
Germany    pork        12
Germany    butter      13
UK         beer        14
France     cheese      15
UK         cheese      16
Germany    butter      17

我的目标是调整这张表，以便我可以为每个国家/地区获得两个更多重复的产品。如果可能，使用一个查询。结果应如下所示：

country    product   count
France     cheese    2
France     wine      2
UK         beer      3
UK         cheese    3
Germany    pork      2
Germany    butter    2

标签： mysqlsql

Answer 1

WITH 
cte1 AS (SELECT DISTINCT 
                country, 
                product, 
                COUNT(*) OVER (PARTITION BY country, product) cnt
         FROM test),
cte2 AS (SELECT country, 
                product, 
                cnt, 
                ROW_NUMBER() OVER (PARTITION BY country ORDER BY cnt DESC) rn
         FROM cte1)
SELECT country, 
       product, 
       cnt 
FROM cte2 
WHERE rn < 3;

小提琴

PS。如果第 2 行和第 3 行（第 4、5、...）行具有相同数量的行，则将返回它们中的不确定（认为是随机的）。