按父ID的顺序选择记录

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

简单的问题..只是无法按照我需要的顺序得到结果集:p

I have a table "categories"

我有一张桌子“类别”

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

4 个解决方案

#1

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

对于一个简单的,可能是次优的,可扩展的解决方案,我建议使用您将拥有的最大级别对其进行硬编码:

For 2 levels only:

仅限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`

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

对于3个级别,虽然你的数据还没有这个 - 路径被省略,因为它会变得丑陋:)

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

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).

一个更全面的解决方案,可以快速选择任何级别的特定类别中的所有项目,这需要对所有写入进行一些工作,实现“正确”和“左”编号概念。但是,对此的进一步讨论几乎肯定超出了你所要求的范围。然而,这是我的经验中唯一一个很好的方法,使这种自引用表非常有用,如果它会变大(可能是1000行以上3到10级)。

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

#2

If those with no parents had null in their parent column, your statement would be very simple:

如果那些没有父项的人在他们的父列中有null,那么你的陈述将非常简单:

SELECT id, name, parent FROM categories order by coalesce(parent, id), id;

If you insist on 0 representing no parent, you can use more verbose CASE WHEN ... THEN ... statement.

如果你坚持0代表没有父母,你可以使用更详细的CASE WHEN ... THEN ...声明。

Edit:

-- Sorting by name instead
select a.id, a.name, a.parent 
from categories a left join categories b on a.parent=b.id 
order by coalesce(b.name, a.name), a.name

#3

This would work, but not recursively.

这可行,但不是递归的。

SELECT 
  b.* 
FROM
  categories a 
  RIGHT JOIN categories b ON b.parent = a.id
ORDER BY
  COALESCE(a.name, b.name), b.name

#4

See if this works:

看看这是否有效:

SELECT Table1.ID, Table1.name, Table1.parent, Table1_1.name, Table1_1.parent
FROM Table1 INNER JOIN Table1 AS Table1_1 ON Table1.ID = Table1_1.parent
ORDER BY Table1.name;

I built this with Micorsoft Access and it looked like what you wanted to me. I think you need a report to group on to visually give you what you want to give to a consumer, but for the sake joining correctly to get to that point this works.

我使用Micorsoft Access构建了它,它看起来就像你想要的那样。我认为您需要一份报告,以便在视觉上为您提供您希望提供给消费者的内容,但为了正确加入以达到这一点,这是有效的。

#1