前N个组中的mysql中的Sql问题 [英] Top N per Group Sql problem in mysql

问题描述

请从类似下面所示的数据集查询每个类别的前N个问题。我看到各种线程，但我有问题适应他们的查询我的具体问题。

Please I am having a problem querying for the Top N per category from a data set resembling the one shown below. I have see various thread on this but I am having problem adapting their query to my specific problem.

+----+---------------------------------+-------+
| ID | Prod                            |Cat Id |
+----+---------------------------------+-------+
|  1 |  kntrn                          |     1 |
|  2 | kntrn e                         |     1 |
|  3 | e spl                           |     1 |
|  4 | spl php                         |     1 |
|  5 | php cicarredgtal                |     1 |
|  6 | cicarredgtal servecounterstrike |     1 |
|  7 | servecounterstrike com          |     1 |
|  8 |  zlv                            |     2 |
|  9 | zlv enter                       |     2 |
| 10 | spl php                         |     2 |
+----+---------------------------------+-------+

我想根据此规则进行分组（1）为每个catid选择Top 3 Prod。

I want to group based on this rule (1) Select Top 3 Prod for each catid.

请注意，在这个意义上，top是所有类别中prod的最高数。

Please do note that top in this sense is the one highest count of prod in all category.

对于上面的例子，spl php是catID 1的最高值，因为它在所有类别中出现两次。

So for the example above spl php is the highest for catID 1 because it occurs twice across all category.

推荐答案

这可能不是很漂亮，但我认为它会工作：

This may not be very pretty, but I think it'll work:

SELECT cat_id, prod, pos FROM (
    SELECT cat_id, pos, prod, if(@last_id = cat_id, @cnt := @cnt + 1, (@cnt := 0 || @last_id := cat_id)) cnt
    FROM (
        SELECT p.cat_id, pseq.cnt pos, pseq.prod
        FROM (
            SELECT prod, count(*) cnt FROM prods GROUP BY prod ORDER BY cnt DESC
        ) pseq
        INNER JOIN prods p ON p.prod = pseq.prod
        ORDER BY cat_id, pseq.cnt DESC
    ) po
) plist
WHERE cnt <= 3;

Based on the above data, this will return:
+--------+-----------+-----+
| cat_id | prod      | pos |
+--------+-----------+-----+
|      1 | spl php   |   2 |
|      1 |  kntrn    |   1 |
|      1 | kntrn e   |   1 |
|      2 | spl php   |   2 |
|      2 |  zlv      |   1 |
|      2 | zlv enter |   1 |
+--------+-----------+-----+

这篇关于前N个组中的mysql中的Sql问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！