mysql - 如何获得至少有 1 个孩子的父母？

Question

我有如下类别表：

category_id  name            parent_id
1            category1       NULL
2            subcategory1    1
3            category2       NULL
4            subcategory2    1
5            subcategory4    NULL
6            subcategory3    1
7            subcategory8    NULL
8            subcategory9    5
9            subcategory5    6
10           subcategory10   6
11           subcategory13   6
12           subcategory6    4
13           subcategory7    4
14           subcategory12   4
15           subcategory17   4
16           subcategory10   4
17           subcategory19   4

我想获取所有至少有一个孩子的父母的数据，并获取所有孩子的数据，每个父母的前 2 行。

以下是我到目前为止所尝试的努力。

SELECT c.category_id, c1.name, 
FROM categories c
LEFT JOIN categories c1 ON c1.category_id = c.category_id where count(c1.id) > 1 
limit 2;

我的预期输出：

category_id parent_id   name            parent_name 
2           1           subcategory1    category1   
4           1           subcategory2    category1
8           5           subcategory9    subcategory4
9           6           subcategory5    subcategory3
10          6           subcategory10   subcategory3
12          4           subcategory6    subcategory2
13          4           subcategory7    subcategory2

标签： mysql

Answer 1

   WITH MyRowSet
    AS
    (
    SELECT NAME
          ,PARENT_ID
            ,ROW_NUMBER() OVER (PARTITION BY PARENT_ID ORDER BY PARENT_ID) AS RowNum
    FROM categories 
    )
    SELECT NAME,PARENT_ID FROM MyRowSet WHERE PARENT_ID IS NOT NULL AND RowNum <= 2