根据整数对SQL中的行进行排序

冰人

我有一个非常简单的表，看起来像这样：

---------------------------------------------------------
| CategoryId | CategoryName             | SubCategoryId |
|------------|--------------------------|---------------|
| 32         | Sports Bar               | 30            |
| 29         | Automotive Services      | 0             |
| 120        | Dance Studio             | 116           |
| 116        | Arts and Hobbies         | 0             |
| 24         | Telecommunications       | 0             |
| 40         | Body Shop                | 29            |
| 41         | Tires                    | 29            |
| 30         | Restaurant/Tavern        | 0             |
| 60         | Coffee House             | 30            |
---------------------------------------------------------

在SubCategoryId决定了的类别，类别应归入。如果为0，则表示其为顶级类别。例如，Tires将属于类别ID为的类别29，即Automotive Services。

我的问题是，我该如何对它进行排序，以使顶层类别排在第一位，然后是该类别下的子类别。例如，在上表中，我希望将其排序为：

---------------------------------------------------------
| CategoryId | CategoryName             | SubCategoryId |
|------------|--------------------------|---------------|
| 116        | Arts and Hobbies         | 0             |
| 120        | Dance Studio             | 116           |
| 29         | Automotive Services      | 0             |
| 40         | Body Shop                | 29            |
| 41         | Tires                    | 29            |
| 30         | Restaurant/Tavern        | 0             |
| 60         | Coffee House             | 30            |
| 32         | Sports Bar               | 30            |
| 24         | Telecommunications       | 0             |
---------------------------------------------------------

我尝试了以下查询，但收到错误：

SELECT * FROM dbo.Category CAT 
ORDER BY (SELECT CategoryId FROM Category CAT2 WHERE CAT.CategoryId=CAT2.SubCategoryId), CategoryName

布莱恩·德米利亚（Brian DeMilia）

with cats as
 (select categoryname,
         categoryid,
         row_number() over(order by categoryname) as cat_rn
    from tbl
   where subcategoryid = 0)
select x.*
  from (select case
                 when c.cat_rn is null
                 then c2.categoryid
                 else t.categoryid
                  end as par,
               t.categoryid,
               t.categoryname,
               t.subcategoryid
          from tbl t
          left join cats c
            on t.categoryid = c.categoryid
          left join cats c2
            on t.subcategoryid = c2.categoryid) x
  join cats c
    on x.par = c.categoryid
 order by c.cat_rn,
          case when x.subcategoryid = 0 then '0' else x.categoryname end