mysql - 按名称排序，然后按父级排序

Question

我有一个表，其中包含如下所示的设置列表:

CREATE TABLE `widget_settings` (
    `setting_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `widget_id` INT(10) UNSIGNED NOT NULL,
    `setting_parent` INT(10) UNSIGNED NOT NULL DEFAULT '0',
    `setting_name` CHAR(50) NOT NULL,
    `setting_type` CHAR(50) NOT NULL,
    `setting_default` CHAR(50) NOT NULL,
    PRIMARY KEY (`setting_id`)
)

我想按setting_name对行进行排序，并且某些设置有子项，因此在设置列表之后它是子项，并且子项的名称为setting_parent ，所以基本上我想要的输出如下所示:

setting_name       | setting_id      | setting_parent
-------------------------------------------------------
A                  | 1               | 0
B                  | 2               | 0
YA                 | 4               | 2
YB                 | 5               | 2
C                  | 20              | 0
ZA                 | 25              | 20

我希望这会起作用，但事实并非如此:

select * from widget_settings 
where widget_id = 1
order by setting_name, setting_parent

这是SQLFiddle

Answer 1

select a.*
from widget_settings a
left join widget_settings b
  on b.widget_id = a.widget_id
  and b.setting_id = a.setting_parent
where a.widget_id = 1
order by 
  ifnull(b.setting_name, a.setting_name),
  if(b.setting_name is null, '', a.setting_name)

在 SQL Fiddle 上进行测试

这里是如何构造 order by 的:

A  ''
B  ''
B  YA   <-- parent name goes to first position & children name to second
B  YB
C  ''
C  ZA