选择每个类别的5个子类别

Question

我有一个表格product_category，它有id、name、parent_id和level这些字段。
例如，类别农业的id=75，level=1，parent_id=NULL。子类别玉米、小麦、黑麦等的level=2，而且parent_id=75。
在我的网站上，我想显示顶级分类，并在每个分类下面仅显示5个子分类。但是查询检索它们比我想象的要困难。
如果我执行以下查询：

SELECT a.name, a.parent_id FROM product_category a
WHERE (
    SELECT b.level
    FROM product_category b
    WHERE b.id = a.parent_id
    LIMIT 1
) = 1

我要检索所有的一级类别和子类别，但是有成千上万个子类别，所以当我只需要每个类别的前5个时，这将是非常不必要的昂贵。

如果我执行以下操作：

SELECT a.name, a.parent_id FROM product_category a
WHERE (
    SELECT b.level
    FROM product_category b
    WHERE b.id = a.parent_id
    LIMIT 1
) = 1
LIMIT 5

它仅检索5个子类别，而不是每个顶级类别的5个子类别。

然后我想以以下方式完成：

(
     SELECT a.name, a.parent_id FROM product_category a
     WHERE parent_id = 12
     LIMIT 5
) UNION (
     SELECT a.name, a.parent_id FROM product_category a
     WHERE parent_id = 21
     LIMIT 5
) UNION (     
     SELECT a.name, a.parent_id FROM product_category a
     WHERE parent_id = 75
     LIMIT 5
) UNION (
.
.
.

这看起来非常混乱和硬编码，但这是我现在能想到的唯一解决方案。还有其他解决方法吗？

谢谢！

- federico-t

---

已经有几个很好的例子在SO上了。搜索mysql中的递归查询或带mysql的分层查询。这是一个例子。http://stackoverflow.com/questions/14266697/tricky-recursive-mysql-query 但你会发现MySQL在这种类型的查询中受到限制。在这种情况下，最好在SQL之外使用多个调用来完成结构。 - xQbert

@xQbert 我认为在这种情况下进行多次调用并不是最好的选择，因为我有许多“顶级”类别，这意味着每个进入前台页面的用户都需要与数据库建立太多连接。 - federico-t

Answer 1

这里有一个示例，可以返回每个根目录最多两个子目录：

select  parent.name as Category
,       child.name as SubCategory
from    (
        select  name
        ,       parent_id
        ,       @rn := if(@cur = parent_id, @rn+1, 1) as rn
        ,       @cur := parent_id
        from    product_category pc
        join    (select @rn := 0, @cur := '') i
        where   level = 2
        order by
                parent_id
        ,       id
        ) as child
join    product_category as parent
on      child.parent_id = parent.id
where   child.rn < 3

Live example at SQL Fiddle.

- Andomar

---

谢谢，太棒了！我把“<3”改成“<6”，让它在每个根类别下返回5个子类别。 - federico-t

Answer 2

这个解决方案按字母顺序优先考虑子结果...

SELECT * FROM product_category;
+-----+---------------------+-----------+-------+
| id  | name                | parent_id | level |
+-----+---------------------+-----------+-------+
|  75 | Agriculture         |      NULL |     1 |
|  76 | Corn                |        75 |     2 |
|  77 | Wheat               |        75 |     2 |
|  78 | Rye                 |        75 |     2 |
|  85 | Vehicles            |      NULL |     1 |
|  86 | Cars                |        85 |     1 |
|  87 | Planes              |        85 |     1 |
|  88 | Trains              |        85 |     1 |
|  95 | Painters            |      NULL |     1 |
|  96 | Surrealists         |        95 |     2 |
|  97 | Impressionists      |        95 |     2 |
|  98 | Post-Impressionists |        95 |     2 |
|  99 | Max Ernst           |        96 |     3 |
| 100 | Claude Monet        |        97 |     3 |
| 101 | Gauguin             |        98 |     3 |
| 102 | Van Gogh            |        98 |     3 |
+-----+---------------------+-----------+-------+

SELECT a.*
  FROM
     ( SELECT x.*
            , y.name subcategory
         FROM product_category x
         JOIN product_category y
           ON y.parent_id = x.id
        WHERE x.parent_id IS NULL
     ) a
  JOIN
     ( SELECT x.*
            , y.name subcategory
         FROM product_category x
         JOIN product_category y
           ON y.parent_id = x.id
        WHERE x.parent_id IS NULL
     ) b
    ON b.id = a.id
   AND b.subcategory <= a.subcategory
 GROUP
    BY a.id,a.subcategory
HAVING COUNT(*) <= 2;
+----+-------------+-----------+-------+---------------------+
| id | name        | parent_id | level | subcategory         |
+----+-------------+-----------+-------+---------------------+
| 75 | Agriculture |      NULL |     1 | Corn                |
| 75 | Agriculture |      NULL |     1 | Rye                 |
| 85 | Vehicles    |      NULL |     1 | Cars                |
| 85 | Vehicles    |      NULL |     1 | Planes              |
| 95 | Painters    |      NULL |     1 | Impressionists      |
| 95 | Painters    |      NULL |     1 | Post-Impressionists |
+----+-------------+-----------+-------+---------------------+

- Strawberry

---

+1 对于大型数据集，double 自连接的性能表现不佳。但是确实有非常创造性的解决方案。 - Andomar