SQL选择倒排树

Question

你好，我在sql中有树结构。逻辑是标准的：SomeID、ParentID、其他字段。 我有选择过程，它选择这样的数据：

1.
1.1
1.1.1

等等。

如何编写选择过程，以获得反转结果（首先选择最深的分支，最后选择根分支），如下所示：

1.1.1.
1.1.
1.
2.2.2.2.2.
2.2.2.2.
2.2.2.
2.2.
2.

等等。

非逆向选择看起来像这样（我使用 SqlServer 2008）：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Object_SelectDownByRoot]
@ObjectID int

AS
WITH tree (ObjectID, ParentID, ObjectName, ObjectCode, DistrictID, DistrictName, 
                      CityName, RegionName, StreetName, StreetID, AddressID, ObjectTypeName, 
                      RouteName, ObjectTypeID, RouteID, AvrgTempIn, Area, Volume, 
                      ElectricPower, ObjectStatusName, ObjectStatusID, [ControlRoom?], DateBuild, 
                      [Floor], EncloseName, EncloseID, MaintenanceEval, AdministratorID, 
                      Administrator, ElectricityPerson, ElectricityPersonID, 
                      HeatingPersonID, HeatingPerson, HouseNo, FlatNo, ZIP, 
                      AddressStreet, RouteCode, RouteDescription, 
                      AddressDescription, StreetID2, CityID, AddressCityName) AS
    (
        SELECT  
        ObjectID, ParentID, ObjectName, ObjectCode, DistrictID, DistrictName, 
                          CityName, RegionName, StreetName, StreetID, AddressID, ObjectTypeName, 
                          RouteName, ObjectTypeID, RouteID, AvrgTempIn, Area, Volume, 
                          ElectricPower, ObjectStatusName, ObjectStatusID, [ControlRoom?], DateBuild, 
                          [Floor], EncloseName, EncloseID, MaintenanceEval, AdministratorID, 
                          Administrator, ElectricityPerson, ElectricityPersonID, 
                          HeatingPersonID, HeatingPerson, HouseNo, FlatNo, ZIP, 
                          AddressStreet, RouteCode, RouteDescription, 
                          AddressDescription, StreetID2, CityID, AddressCityName
         FROM dbo.[ObjectQ] ofs
         WHERE( ObjectID = @ObjectID )

         UNION ALL

         SELECT     ofs.ObjectID, ofs.ParentID, ofs.ObjectName, ofs.ObjectCode, ofs.DistrictID, ofs.DistrictName, 
                          ofs.CityName, ofs.RegionName, ofs.StreetName, ofs.StreetID, ofs.AddressID, ofs.ObjectTypeName, 
                          ofs.RouteName, ofs.ObjectTypeID, ofs.RouteID, ofs.AvrgTempIn, ofs.Area, ofs.Volume, 
                          ofs.ElectricPower, ofs.ObjectStatusName, ofs.ObjectStatusID, ofs.[ControlRoom?], ofs.DateBuild, 
                          ofs.[Floor], ofs.EncloseName, ofs.EncloseID, ofs.MaintenanceEval, ofs.AdministratorID, 
                          ofs.Administrator, ofs.ElectricityPerson, ofs.ElectricityPersonID, 
                          ofs.HeatingPersonID, ofs.HeatingPerson, ofs.HouseNo, ofs.FlatNo, ofs.ZIP, 
                          ofs.AddressStreet, ofs.RouteCode, ofs.RouteDescription, 
                          ofs.AddressDescription, ofs.StreetID2, ofs.CityID, ofs.AddressCityName
          FROM dbo.[ObjectQ] ofs
          JOIN tree ON tree.ObjectID = ofs.ParentID
    )

    SELECT  
    ObjectID, ParentID, ObjectName, ObjectCode, DistrictID, DistrictName, 
                      CityName, RegionName, StreetName, StreetID, AddressID, ObjectTypeName, 
                      RouteName, ObjectTypeID, RouteID, AvrgTempIn, Area, Volume, 
                      ElectricPower, ObjectStatusName, ObjectStatusID, [ControlRoom?], DateBuild, 
                      [Floor], EncloseName, EncloseID, MaintenanceEval, AdministratorID, 
                      Administrator, ElectricityPerson, ElectricityPersonID, 
                      HeatingPersonID, HeatingPerson, HouseNo, FlatNo, ZIP, 
                      AddressStreet, RouteCode, RouteDescription, 
                      AddressDescription, StreetID2, CityID, AddressCityName
    FROM tree

Answer 1

如果你不能进行递归那么我只能想到另一种解决方案。我确信这不是最佳的，但是。您可以执行上面所做的操作，并将该数据插入到具有 2 个额外列的临时表中。一列将保存您的父 ID，因为您似乎仍在最高级别上按降序排序（因为您在所有 2 之前拥有所有 1），而另一列可以仅保存种子 Identity 整数。然后，您可以查询表并按升序对原始父 ID（第一个数字）进行排序，然后对种子 Identity 整数按降序进行排序。据我所知，这可行，但效率低下。