MYSQL查询数据并组合，分组和订购结果

I am having trouble getting a query to work effectively which only queries 1 table. Here is an example of the table data:

我无法使查询有效地工作,只查询1个表。以下是表数据的示例:

ID    NAME     PARENT_ID    SORT_ORDER
1     Home     NULL         1
2     Contact  NULL         3
3     Service  NULL         2
4     Service1 3            0
5     Service3 3            2
6     Service2 3            1

What I would like to do is to return the data from this table with results that have a PARENT_ID appearing under the result with that ID, and to have all results then display in their SORT_ORDER. Here is how I would like the query to result the above data:

我想要做的是返回此表中的数据,其结果是PARENT_ID出现在具有该ID的结果下,并使所有结果显示在他们的SORT_ORDER中。以下是我希望查询得出上述数据的方法:

ID   NAME     PARENT_ID    SORT_ORDER
1    Home     NULL         1
3    Service  NULL         2
4    Service1 3            0
6    Service2 3            1
5    Service3 3            2
2    Contact  Null         3

Any feedback to make this happen is very welcome.

任何反馈都是非常受欢迎的。

Kind regards,

Paul

1 个解决方案

#1

If I understand your logic correctly, you could use this:

如果我理解你的逻辑,你可以使用这个:

SELECT
  t1.*
FROM
  yourtable t1 LEFT JOIN yourtable t2
  ON t1.PARENT_ID = t2.ID
ORDER BY
  COALESCE(t2.SORT_ORDER, t1.SORT_ORDER),
  t1.PARENT_ID IS NOT NULL,
  SORT_ORDER

Please see fiddle here.

请看这里的小提琴。

#1