我如何先由父母订购然后由孩子订购?

问题描述

我正在尝试编写我的 SQL Server 2008 查询，这样我就可以根据需要循环遍历我的输出和输出标头.我已经多次以错误的方式完成这些工作，并且让 ColdFusion 在页面内完成了繁重的工作，但需要在 SQL Server 中完成.

I am trying to write my SQL Server 2008 query in such a way that I can just loop through my output and output headers as needed. I've done this stuff the wrong way many times and had ColdFusion do the hard work within the page, but need this done in SQL Server.

FeatureID ParentID Feature
--------------------------
1         0        Apple      
2         0        Boy 
3         2        Charles
4         1        Daddy
5         2        Envelope
6         1        Frankfurter

我希望我的查询结果集如下所示:

I want my query resultset to look like this:

FeatureID ParentID Feature
--------------------------
1         0        Apple      
4         1        Daddy
6         1        Frankfurter
2         0        Boy 
3         2        Charles
5         2        Envelope

如果 ParentID 为 0，则表示它是一个主要类别.如果 ParentID 大于 0，则表示它是次要类别，是父级的子级.

If the ParentID is 0, it means that it's a major category. If the ParentID is greater than 0, it means it's a minor category, a child of the parent.

所以父母需要按 A-Z 排序，孩子需要按 A-Z 排序.

So the parents need to be ordered A - Z and the children need to be ordered A-Z.

你能帮我正确订购吗?

SELECT FeatureID, ParentID, Feature
FROM Features
ORDER BY

推荐答案

从您的评论中，如果您知道只有两个级别，那么有一个简单的解决方案:

From your comment, if you know there are only two levels, there is an easy solution:

select  *
from    @Features feat
order by
        case 
        when ParentID = 0 
        then Feature 
        else    (
                select  Feature 
                from    @Features parent 
                where   parent.FeatureID = feat.ParentID
                ) 
        end
,       case when ParentID = 0 then 1 end desc
,       Feature

1. 按根元素的名称排序:对于根，这是 Feature 列.对于孩子，使用子查询查找根的名称.
2. 对根进行排序
3. 按名称对子项进行排序

SE Data 示例.

这篇关于我如何先由父母订购然后由孩子订购?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！