mySQL 分层分组排序 [英] mySQL hierarchical grouping sort

问题描述

我有一个基本如下所示的架构:

I have a schema that essentially looks like this:

CREATE TABLE `data` (
  `id` int(10) unsigned NOT NULL,
  `title` text,
  `type` tinyint(4),
  `parent` int(10)
)

type 字段只是一个枚举，其中 1 是父类型，2 是子类型(实际上有很多类型，有些应该像父母一样，有些应该像孩子一样).parent 字段表示一条记录是另一条记录的子记录.

The type field is just an enum where 1 is a parent type, and 2 is a child type (in actuality there are many types, where some should behave like parents and some like children). The parent field indicates that a record is the child of another record.

我知道这对于我想要构建的查询来说可能并不理想，但这是我必须使用的.

I know this is probably not ideal for the query I want to build, but this is what I have to work with.

我想对数据进行排序和分组，以便父记录按title 排序，并在每个父记录下分组按title 排序的子记录.像这样:

I would like to sort and group the data so that the parent records are sorted by title, and grouped under each parent is the child records sorted by title. Like so:

 ID | title       |type |parent 
--------------------------------
 4  | ParentA     | 1   |
 2  | ChildA      | 2   | 4
 5  | ChildB      | 2   | 4
 7  | ParentB     | 1   |
 9  | ChildC      | 2   | 7
 1  | ChildD      | 2   | 7

<小时>

** 编辑 **

我们应该能够将 type 字段从图片中完全去掉.如果 parent 不为空，那么它应该被分组在它的父级之下.

We should be able to take the type field out of the picture entirely. If parent is not null then it should be grouped underneath it's parent.

推荐答案

SELECT * FROM `data` ORDER BY COALESCE(`parent`, `id`), `parent`, `id`

这篇关于mySQL 分层分组排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！