MySQL：如何查询父子？ [英] MySQL: how to query parent-child?
问题描述
假设以下表记录：
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 |
--------------------------
我想得到，例如，前10个父记录（这些记录与foo_parent_id = NULL）紧接着，例如，该父记录的前2个子记录。所以，我正在寻找这样的结果：
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:
1, NULL
3, 1
5, 1
2, NULL
4, 2
7, 2
9, NULL
如何查询这样的结果？
推荐答案
但它是基于大量的假设你的数据设置的方式。
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.
SELECT f.foo_id,f.foo_parent_id FROM foo f
foo f
- 给我最前X个parent_ids的数字
（这很好，您只需调整LIMIT 10即可更改要显示的父级数量）。
--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)
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
父项
f.foo_parent_id is null
order by isnull(f.foo_parent_id,f.foo_id)*100 + f.foo_id
所以我们这里做的基本上是通过parent_id列排序，柱子下面有轻微的扭曲。如果parentid列为NULL，那么我们使用实际的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:
==============================================================================
| 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
----------------------------------------------------------------------
然后我们乘以该顺序列* 100
Then we multiply that ordering column *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
----------------------------------------------------------------------
，最后我们向它添加我们的foo_id列
and lastly we add our foo_id column to it
==============================================================================
| 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
----------------------------------------------------------------------
我们走！
这篇关于MySQL：如何查询父子？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
