MySQL:如何查询父子关系?

Assume the following table records:

假设以下表记录:

TABLE: foo
==========================
| foo_id | foo_parent_id |
==========================
| 1      | NULL          |
| 2      | NULL          |
| 3      | 1             |
| 4      | 2             |
| 5      | 1             |
| 6      | 1             |
| 7      | 2             |
| 8      | 1             |
| 9      | NULL          |
--------------------------

I want to get, say, the first 10 parent records (those records with foo_parent_id = NULL) immediately followed by, say, the first 2 child records of that parent record. So, I'm looking for a result like this:

例如，我想立即获得前10个父记录(foo_parent_id = NULL的记录)，然后是该父记录的前2个子记录。所以，我在寻找这样的结果:

1, NULL
3, 1
5, 1
2, NULL
4, 2
7, 2
9, NULL

How do I query something like this?

我如何查询这样的东西?

2 个解决方案

#1

Here's one idea. But it's based on lots of assumptions about the way your data is setup. Ever increasing IDs down the tree, only two levels, etc.

这是一个想法。但它是基于对数据设置方式的假设。在树下增加id，只有两个级别，等等。

SELECT f.foo_id,f.foo_parent_id FROM foo f
foo f

--give me the top X number of parent_ids (This is good, you just adjust the LIMIT 10 to vary the number of parent levels to show)

——给出parent_ids的前X个数(这很好，只需调整界限10，以改变要显示的父级别的数量)

INNER JOIN 
(select foo_id from foo where foo_parent_id is null order by foo_parent_id 
LIMIT 10
) top_foo_parent
      on isnull(f.foo_parent_id,f.foo_id) = top_foo_parent.foo_id
WHERE

(This part is kind of hacky, as you have to put an ever longer string of these to get past two children)

(这部分有点老套，你得放一长串这样才能通过两个孩子)

--it's the first child, or...

——是第一个孩子，还是……

(f.foo_id in (select MIN(foo_id) from foo fc1 where fc1.foo_parent_id =f.foo_parent_id)
 )
 or

--it's the second child, or...

——是第二个孩子，还是……

(f.foo_id in (select MIN(foo_id) from foo fc1 where fc1.foo_parent_id =f.foo_parent_id  and fc1.foo_id not in (select MIN(foo_id) from foo fc2 where fc2.foo_parent_id=f.foo_parent_id))
 )
 or 

--it's the parent

——这是父母

 f.foo_parent_id is null
order by isnull(f.foo_parent_id,f.foo_id)*100 + f.foo_id

So what we're doing here is basically ordering by the parent_id column and then the child columns underneath it with a slight twist. If the parentid column is NULL then we use the actual ID. This means that for ordering purposes our table looks like this:

这里我们要做的就是通过parent_id列进行排序，然后在它下面的子列进行稍微的扭转。如果parentid列为空，则使用实际的ID。

==============================================================================
| foo_id | foo_parent_id |   isnull(f.foo_parent_id,f.foo_id)
==============================================================================
| 1      | NULL           |         (1)
| 2      | NULL           |         (2)
| 3      |  1             |         1
| 4      |  2             |         2
| 5      |  1             |         1
| 7      |  2             |         2
----------------------------------------------------------------------

Then we multiply that ordering column *100

然后我们把有序列乘以100

==============================================================================
| foo_id | foo_parent_id |   isnull(f.foo_parent_id,f.foo_id)*100
==============================================================================
| 1      | NULL           |         100
| 2      | NULL           |         200
| 3      |  1             |         100
| 4      |  2             |         200
| 5      |  1             |         100
| 7      |  2             |         200
----------------------------------------------------------------------

and lastly we add our foo_id column to it

最后添加foo_id列

==============================================================================
| foo_id | foo_parent_id |   isnull(f.foo_parent_id,f.foo_id)*100 + foo_id
==============================================================================
| 1      | NULL           |         101
| 2      | NULL           |         202
| 3      |  1             |         103
| 4      |  2             |         204
| 5      |  1             |         105
| 7      |  2             |         207
----------------------------------------------------------------------

Now we order the table by that virtual column and...

现在我们通过虚拟列对表进行排序，然后…

==============================================================================
| foo_id | foo_parent_id |   ORDER BY isnull(f.foo_parent_id,f.foo_id)*100 + foo_id
==============================================================================
| 1      | NULL           |         101
| 3      |  1             |         103
| 5      |  1             |         105
| 2      | NULL           |         202    
| 4      |  2             |         204
| 7      |  2             |         207
----------------------------------------------------------------------

There we go!

我们走吧!

#2

I'd like to offer another approach at answering this question:

我想提出另一种方法来回答这个问题:

SET @topN = 1;
SELECT foo_id, foo_parent_id
FROM (
    SELECT 
          f.foo_id
        , f.foo_parent_id
        , IFNULL(f.foo_parent_id, f.foo_id) AS grp_rank
        , CASE 
            WHEN f.foo_parent_id IS NULL 
                THEN @topN:= 1 
            ELSE 
                @topN:=@topN+1 
            END topN_rank
        , f_parent.foo_id AS f_parent_foo_id
    FROM 
        foo AS f
        RIGHT JOIN (
            SELECT foo_id 
            FROM foo 
            WHERE foo_parent_id IS NULL 
            ORDER BY foo_id LIMIT 10
        ) f_parent
        ON f_parent.foo_id = IFNULL(f.foo_parent_id, f.foo_id)
    ORDER BY grp_rank, f.foo_id
) AS foo_derived
WHERE topN_rank <= 3

General Notes:

将军指出:

I want to get, say, the first 10 parent records <-- SUBQUERY "f_parent"

比如说，我想获取前10个父记录<——子查询"f_parent"

followed by, say, the first 2 child records of that parent record. <-- topN <= 3 (PARENT + 2 CHILDREN)

然后，例如，父记录的前两个子记录。<- topN <= 3(父+ 2子)

How it works:

它是如何工作的:

1. Create an extra column for foo_id to foo_parent_id integration grp_rank
2. 创建一个额外的列，为foo_id到foo_parent_id集成grp_rank。
3. Join to a subquery pulling only the top 10 parents (assuming the order is by their foo_id values)
4. 连接到只提取前10个父类的子查询(假设顺序取决于它们的foo_id值)
5. Remember to sort by that extra column followed by foo_id for 'parent..followed by..first two children'
6. 记住要按“parent”的额外列后跟foo_id进行排序。紧随其后的是. .第一次两个孩子
7. Create another column than ranks the rows that will be sorted resetting via foo_parent encounters. NULL foo_parent_id resets the ranking. This new column topN_rank is impacted by the sorting.
8. 创建另一个列，对通过foo_parent encounters重新设置的行进行排序。NULL foo_parent_id重置排名。这个新的列topN_rank受到排序的影响。
9. Nest that query to be able to derive topN_rank and to return only the needed columns.
10. 嵌套该查询，以便能够派生topN_rank并仅返回所需的列。
11. Filter by topN_rank column to get the top N rows for each group, prohibiting more than 3 per group.
12. 通过topN_rank列进行筛选，得到每个组的前N行，每个组禁止超过3行。