SQL嵌套顺序由? [英] 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.

我有一个类别和子类别的表格.他们每个人都有一个ID和一个父母ID.如果是顶级类别，则父ID为0.子类别的父ID设置为 它的父类别ID.

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

我正在尝试编写一个SQL查询，该查询将按此顺序为我提供所有类别:

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

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

SELECT * FROM categories ORDER BY display_order 

这在SQL中是可能的，还是我需要使用多个查询

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

谢谢， 布拉德

推荐答案

类似的方法可能有效:

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)

第一个ORDER BY条件将父母和孩子分类在一起；然后第二个确保父母先于子女；第三类对孩子们进行排序.

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.

这篇关于SQL嵌套顺序由?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！