我有一个包含类别列表的表
表格结构
- ID
- category_name
- parent_category_ID // 0 indicates no parent
假设表中有以下数据
ID | category_name | parent_category_ID
1 | verbs | 0
2 | adjectives | 0
3 | nouns | 0
4 | go | 1
5 | color | 3
6 | bag | 3
7 | good | 2
8 | play | 1
9 | polite | 2
10 | t1 | 5
11 | t2 | 5
12 | t3 | 8
我希望结果按此顺序
ID | category_name | parent_category_ID
1 | verbs | 0
4 | go | 1
8 | play | 1
12 | t3 | 8
2 | adjectives | 0
7 | good | 2
9 | polite | 2
3 | nouns | 0
5 | color | 3
10 | t1 | 5
11 | t2 | 5
6 | bag | 3
这将使结果看起来像
verbs
--- go
--- play
--- --- t3
adjectives
--- good
--- polite
nouns
--- color
--- --- t1
--- --- t2
--- bag
事实上,我不知道如何开始做这件事,因为我的SQL
经验仍然是初学者。
不太确定我是否理解正确,但尝试一下:
SELECT
id,
category_name,
parent_category_ID
FROM yourTableName
ORDER BY CASE
WHEN parent_category_ID = 0
THEN ID
ELSE parent_category_ID END, id
演示:SQL FIDDLE