在MySQL中检索父子有序数据

I have a table with say following fields

我有一张桌子上写着以下字段

id, name, sub_id
1, test1, ""
2, test2, ""
3, test3, ""
4, test4, ""
5, test5, 1
6, test6, 1
7, test7, 2
8, test8, 3

I want to retrieve the data from this table ordered in such a way that it is ordered by id and then sub_id if it exists. for example:

我想从这个表中检索数据，这个数据的排序方式是按id排序，然后是sub_id（如果存在的话）。例如：

id, name, sub_id
1, test1, ""
5, test5, 1
6, test6, 1
2, test2, ""
7, test7, 2
3, test3, ""
8, test8, 3
4, test4, ""

I tried group by and order by but that didnt work.

我尝试了分组并按顺序但是没有用。

2 个解决方案

#1

It took me a while to understand, that the sub_id references the same table again. After that i realized you need to join the table on itself.

我花了一段时间才明白，sub_id再次引用同一个表。在那之后，我意识到你需要自己加入桌子。

Try this query

试试这个查询

SELECT
  a.*
FROM
  `some_table` AS a
  LEFT JOIN `some_table` AS b
    ON
      b.id = a.sub_id
ORDER BY
  IF (b.id IS NULL, 0, a.sub_id) ASC,
  a.id ASC

#2

Your example doesn't show ordering by id then sub_id, but if you wanted to it would be easier to do:

您的示例不显示按id然后sub_id排序，但如果您想要它将更容易做：

SELECT * FROM some_table ORDER BY id,ifnull(sub_id,0);

I'm assuming that "" is a null value, otherwise you would need a different function than ifnull.

我假设“”是一个空值，否则你需要一个不同于ifnull的函数。

#1