mysql - 每组前 N 行仅获得 1 行

Question

我试图显示每个类别的前 3 个(或任意数量)住房。顶部意味着访问量最大。所以如果我有一个像这样的表:

+------------+--------------------+--------+
| housing_id | category           | visits |
+------------+--------------------+--------+
|          7 | cat                |      2 |
|          8 | New Category       |      1 |
|         10 | bead and breakfast |      1 |
|         11 | bead and breakfast |      4 |
|         15 | 2                  |      3 |
|         16 | 2                  |      1 |
|         17 | New Category       |      1 |
|         18 | cat                |      1 |
+------------+--------------------+--------+

我和我想选择每个类别中访问量最大的前 3 个住房，所以我这样做了。

select housing_id, category, visits
from
    (select housing_id, category, visits,
        @category_rank := if(@current_category = category, @country_rank + 1, 1) as category_rank,
        @current_category := category
    from visit_counts
    order by category, visits desc
    ) ranked
where category_rank <= 3;

我得到:

+------------+--------------------+--------+
| housing_id | category           | visits |
+------------+--------------------+--------+
|         15 | 2                  |      3 |
|         11 | bead and breakfast |      4 |
|          7 | cat                |      2 |
|          8 | New Category       |      1 |
+------------+--------------------+--------+

但我想要:

+------------+--------------------+--------+
| housing_id | category           | visits |
+------------+--------------------+--------+
|         15 | 2                  |      3 |
|         16 | 2                  |      1 |
|         11 | bead and breakfast |      4 |
|         10 | bead and breakfast |      1 |
|          7 | cat                |      2 |
|         18 | cat                |      1 |
|          8 | New Category       |      1 |
|         17 | New Category       |      1 |
+------------+--------------------+--------+

Answer 1

您正在使用用户变量但未声明它们。另外，您应该在一个表达式中分配和读取用户变量，因为 MySQL 不保证列计算的顺序(因此分配可能发生在您读取它之前或之后)。

试试这个:

select housing_id, category, visits
from (
    select housing_id, category, visits,
        @category_rank := if(@current_category = category, 
                                @category_rank + 1,
                                if(@current_category := category, 1, 1)
                            ) as category_rank
    from visit_counts, (select @category_rank := 0, @current_category := null) t2
    order by category, visits desc
    ) ranked
where category_rank <= 3;

Demo