按父ID的顺序选择记录 [英] Selecting records in order of parent id

问题描述

一个简单的问题..只是无法按照我需要的顺序获取结果集:p

Simple question.. just can't get the result set in the order I need :p

我有一个表类别"

id    | name     | parent
1       apple      0
2       macintosh  1
3       atari      0
4       st         3
5       lisa       1

我正在尝试选择获取以下结果集:

I'm trying to select get the following result set:

1   apple      0
5   lisa       1
2   macintosh  1
3   atari      0
4   st         3

换句话说，我想要所有行的所有列，其中具有父级的行紧接其父级行，并且所有行均按字母顺序排序.

So in other words I want all columns of all rows, where rows with parents are immediately after their parent row and all are sorted alphabetically.

parent a
 child a
 child b
parent b
 child a

我现在使用的查询不能正确地对父级后面的行进行重新排序

The query I'm using now doesn't correctly re-order the rows after their parents

SELECT a.*, b.* FROM categories a RIGHT JOIN categories b ON b.parent = a.id

推荐答案

对于一个简单的，也许次优可缩放的解决方案，我建议使用您将拥有的最大级别的硬编码对其进行硬编码:

For a simple, perhaps suboptimally-scalable solution, I recommend hard-coding this with the maximum number of levels you will have:

仅适用于2个级别:

SELECT p2.name as `Parent name`, p1.*
FROM categories p1
LEFT JOIN categories p2 on p1.categories_id = p2.id

您实际上是在询问排序问题，因此建议您生成类似路径"的字符串: (有关此查询的示例输出，请参见下文)

You're really asking about sorting, so I'd recommend generating a "path"-like string: (see below for sample output of this query)

SELECT Concat(If(isnull(p2.name),"",Concat("/",p2.name)),"/",p1.name) as `generated path`, p2.name as `Parent name`, p1.*
FROM categories p1
LEFT JOIN categories p2 on p1.parent_id = p2.id
order by `generated path`

对于3个级别，尽管您的数据还没有这个-省略了路径，因为它会变得很丑陋:)

For 3 levels, though your data doesn't have this yet -- path omitted because it will get ugly :)

SELECT p3.name as `Grandparent name`, p2.name as `Parent name`, p1.*
FROM categories p1
LEFT JOIN categories p2 on p1.categories_id = p2.id
LEFT JOIN categories p3 on p2.categories_id = p3.id

一个更全面的解决方案正在实施.但是，对此的进一步讨论几乎可以肯定超出了您所要求的范围.但是，这是我的经验中使这种自引用表变得非常有用的唯一好方法，如果它要变大(也许在1000多个行，具有3到10个级别之后).

A more comprehensive solution for quickly selecting all items in a particular category at any level, which does require some work on all writes, is implementing a 'right' and 'left' numbering concept. But, further discussion on that is almost certainly going beyond the scope of what you're asking. However, that's the only good way in my experience to make this kind of self-referencing table very useful if it's going to get big (maybe after 1000+ rows with 3 to 10 levels).

附录:第二个查询的示例输出:

Addendum: sample output from the second query:

generated path         Parent name         id         name         parent_id
----------------------------------------------------------------------------
/apple                                      1         apple                0
/apple/lisa                  apple          5         lisa                 1
/apple/mac                   apple          2         mac                  1
/atari                                      3         atari                0
/atari/st                    atari          4         st                   3

这篇关于按父ID的顺序选择记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！