Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

mysql - SQL nested order by?

I'm sure that this has been asked before, but I don't know what to call it exactly to find the answer.

I have a table of categories and sub categories. They each have an id and a parent id. If it is a top level category, the parent id is 0. Sub categories have the parent id set to the category id of it's parent.

category_id          # The ID for this record
category_name        # The name of the category
parent_id            # The parent ID for this category
display_order        # Order of categories within their grouping

1 A  0 0     # First primary category
2 a1 1 0     # Subcategory, parent is A, display_order is 0
3 a2 1 1
4 a3 1 2

5 B  0 1     # Second primary category
6 b1 5 0     # Subcategory, parent is B, display_order is 0
7 b2 5 1
8 b3 5 2

I'm trying to write an SQL query that will give me all of the categories in this order:

A, a1, a2, a3, B, b1, b2, b3

SELECT * FROM categories ORDER BY display_order 

Is this possible in SQL, or will I need to use multiple queries

Thanks, Brad

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Something like this might maybe work:

SELECT *
FROM categories
ORDER BY IF(parent_id, parent_id, category_id), parent_id, display_order

but since it can't use an index, it'll be slow. (Didn't test though, might be wrong)

The first ORDER BY condition sorts parents and children together; then the second one ensures the parent precedes its children; the third sorts the children among themselves.

Also, it will obviously work only in the case you directly described, where you have a two-level hierarchy.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...