MySql: 按parent和child排序

Question

我有一个像这样的表格：

+------+---------+-
| id   | parent  |
+------+---------+
| 2043 |    NULL |
| 2044 |    2043 |
| 2045 |    2043 |
| 2049 |    2043 |
| 2047 |    NULL |
| 2048 |    2047 |
| 2049 |    2047 |
+------+---------+

这个表格展示了一个简单的二级“父子”关系。如何使用SELECT语句进行ORDER BY，以获得与上面列表中相同的顺序，也就是先列出第一个父节点，接着列出第一个父节点下的所有子节点，再列出第二个父节点，接着列出第二个父节点下的所有子节点，以此类推（如果我做到了这一点，就可以为子节点添加ORDER BY了...希望如此）。能否实现不添加排序字段?

- Werner

---

我看到id=2043出现了两次，请修复。 - Rick James

Answer 1

包括按ID排序孩子们：

ORDER BY COALESCE(parent, id), parent IS NOT NULL, id

SQL Fiddle 示例

说明：

• COALESCE(parent, id)：首先按（实际上将父行分组）父行的id排序。
• parent IS NOT NULL：将父行放置在组顶部。
• id：最后按所有子行排序（相同的父行，且parent不为null）。

- lc.

---

好像是sqlfiddle的问题。不过，可以试试这个网址：http://www.sqlfiddle.com/#!9/63640/1 - lc.

Answer 2

如果您的表格使用0而不是null来表示没有父级的条目：

id   | parent
-------------
1233 | 0
1234 | 1233
1235 | 0
1236 | 1233
1237 | 1235

使用greatest代替coalesce，并检查值不等于0:

ORDER BY GREATEST(parent, id), parent != 0, id

- mxwltn

---

这个不起作用，我不得不使用这个：ORDER BY COALESCE(IF(parent_id=0,NULL,parent_id), id), parent_id IS NOT NULL, id; - Abeer Sul

Answer 3

这个问题仍然显示在搜索结果的前几位。因此，我想分享我的解决方案，并希望它能帮助更多的人。当您有一个具有许多级父子关系的表时，这种解决方案也适用。虽然它是相当慢的解决方案。顶层的父项使用NULL。

+---------+---------+
| id      | parent  |
+---------+---------+
| 1       | NULL    |
| 2       | 1       |
| 3       | 1       |
| 4       | 2       |
+---------+---------+

在我的方法中，将使用一个递归调用自身的过程，并将路径不断添加到请求的 id 的父级，直到达到 NULL 父级。

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `PATH`(IN `input` INT, OUT `output` VARCHAR(128))
BEGIN

  DECLARE _id INT;
  DECLARE _parent INT;
  DECLARE _path VARCHAR(128);

  SET `max_sp_recursion_depth` = 50;

  SELECT `id`, `parent`
  INTO _id, _parent
  FROM `database`.`table`
  WHERE `table`.`id` = `input`;

  IF _parent IS NULL THEN
    SET _path = _id;
  ELSE
    CALL `PATH`(_parent, _path);
    SELECT CONCAT(_path, '-', _id) INTO _path;
  END IF;

  SELECT _path INTO `output`;

END $$
DELIMITER ;

为了在 ORDER BY 子句中使用结果，您需要编写一个 FUNCTION 来封装 PROCEDURE 的结果。

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `GETPATH`(`input` INT) RETURNS VARCHAR(128)
BEGIN

  CALL `PATH`(`input`, @path);
  RETURN @path;

END $$
DELIMITER ;

现在我们可以使用递归路径来排序表格的顺序。 在我的工作站上，对于具有10000行的表格，仅需要略微超过一秒钟的时间。

SELECT `id`, `parent`, GETPATH(`id`) `path` FROM `database`.`table` ORDER BY `GETPATH`(`id`);

示例输出：

+---------+---------+---------------+
| id      | parent  | path          |
+---------+---------+---------------+
| 1       | NULL    | 1             |
| 10      | 1       | 1-10          |
| 300     | 10      | 1-10-300      |
| 301     | 300     | 1-10-300-301  |
| 302     | 300     | 1-10-300-302  |
+---------+---------+---------------+
5 rows in set (1,39 sec)

- Thomas Lobker

---

在重新阅读初始问题后，我发现它只是询问具有多个后代（兄弟姐妹）的单个父级的2级后代。这个答案超出了要求，并涵盖了我需要的N级后代情况。 - chiliNUT

谢谢！它解决了N级树的问题。 - NoobnSad

它的工作很好，但我有一个建议，路径中的顺序不正确，因为1-10在数字顺序上大于1-2，但在文本顺序上不是这样，所以我会更改行“SELECT CONCAT(_path,'-',_id) INTO _path”，添加LPAD，如“SELECT CONCAT(_path,'-',LPAD(_id,3,0)) INTO _path;”。 - SauloAlessandre

Answer 4

我尝试了上面的解决方案，但对我无效，因为我的表使用0而不是NULL。 我找到了另一种解决方案：在查询中创建一个将父ID和子ID连接起来的列，然后您可以按此列对结果进行排序。

SELECT CONCAT(IF(parent = 0,'',CONCAT('/',parent)),'/',id) AS gen_order
FROM table 
ORDER BY gen_order

- Ndrezas