MySQL根据父ID排序类别 [英] mysql sort category according to parent id

问题描述

这是表结构

id   parent_id   name
1    0           BMW
2    0           Mercedez
3    0           Porsche
4    1           3 Series
5    2           E60
6    1           5 Series
7    3           Cayenne

如何将表格显示为

BMW
3 Series
5 Series
Mercedez
E60
Porsche
Cayenne 

上表显示升序I​​D，后跟与该ID相关联的parent_id，然后转到第二个ID，依此类推.我需要在单个查询中，是否可以这样做?

The above table shows ascending id followed by parent_id associated with that id, then go to second id and so on. I need in a single query, is it possible to do as such?

推荐答案

尝试一下:

SELECT
  name,
  CASE WHEN parent_id = 0 THEN id ELSE parent_id END AS Sort
FROM
  cars
ORDER BY
  Sort,
  id

http://sqlfiddle.com/#!2/9b05f/3

鉴于此答案一直在增加，我重新考虑了这个问题并发现了一个缺陷.如果由于某种原因，父级的ID高于子级的ID，则排序会混乱.仅当父ID的编号小于所有子ID的编号时，以上查询才有效.

Given that this answer keeps getting upvotes, I revisited the question and found a flaw. If, for some reason, the parent has a higher ID than the child, the ordering gets messed up. The above query only works if the parent ID is a lower number than all the children.

为演示该问题，请想象表看起来像这样:

To demonstrate the problem, imagine the table looked like this:

id   parent_id   name
8    0           BMW
2    0           Mercedez
3    0           Porsche
4    8           3 Series
5    2           E60
6    8           5 Series
7    3           Cayenne

现在请注意，BMW的id为 8 ，而不是 1 .结果将如下所示:

Notice now that BMW has an id of 8 instead of 1. The result will look like this:

Mercedez
E60
Porsche
Cayenne
3 Series
5 Series
BMW

请注意， BMW 显示在列表的底部，之后其子项！这是因为id的二级排序顺序，并且如果父ID碰巧高于任何子代，则父代可能不会出现在子代之上.

Notice above that BMW shows up at the bottom of the list, after its children! This is because the secondary sorting orders by id, and if the parent ID happens to be higher than any children, the parent may not show up on top of the children.

此查询将解决该问题:

SELECT
  name
FROM
  cars
ORDER BY
  CASE WHEN parent_id = 0 THEN id ELSE parent_id END, -- another way of writing it: ISNULL(NULLIF(parent_id, 0), id)
  parent_id,
  id

http://sqlfiddle.com/#!2/6d6d73/3

要解释这里发生的情况，请先按父行的id字段和子行的parent_id字段排序.如果按此顺序进行排序，则所有子项都将与其父项归为一组，并且整个列表将由父项的id字段进行排序.

To explain what's going on here, you first order by the parent row's id field and the children rows' parent_id field. If you order by that, all the children will be grouped up with their parent, and the overall list will be ordered by the parent's id field.

但是，这并没有设置家庭内部的排序，因此父母可以出现在家庭内部的任何位置(父母可以出现在顶部，或者可以出现在中间，或者可以是最后).

But that doesn't set the sorting within the families, so the parent could show up anywhere within the family (the parent could show up at the top, or it could be in the middle, or it could be last).

这是其他两个排序字段的输入位置.第二个字段按parent_id排序，父行的parent_id字段始终为0.安全地假设您的ID字段始终为正，这意味着父记录将始终显示在家族的顶部.其余子项的parent_id值均相同，因此第三个排序字段通过id字段对家庭中的子项进行排序.也可以将其更改为name，具体取决于您希望孩子如何排序.

That's where the other two sorting fields come in. The second field sorts by parent_id, and the parent row's parent_id field is always 0. Safely assuming that your ID fields are always positive, this means that the parent record will always show up on top within the family. The rest of the children will all have the same value for parent_id, so the third sorting field orders the children within the family by their id field. This could also be changed to name, depending on how you want the children sorted.

这篇关于MySQL根据父ID排序类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！