Question

Hello i have tree structure in sql. Logics is standard: SomeID, ParentID, other fields. I have select procedure, which selects data like this:

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.

和如此。

非敌对性选择类似(我2008年使用SqlServer):

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

如果你不会递归，我只能想到另一个解决方案。我确定它不是最优的，但是你可以像上面那样，将数据插入到一个临时表中，该表有两个额外的列。一个列将持有你的父ID，因为似乎你仍在按照最高级别的降序排序（因为你所有的1都在所有的2之前），另一个列可以仅持有一个种子标识整数。然后你可以查询表格并按照父ID的升序排序，然后按照种子标识整数的降序排序。据我所知，这应该是可以的，但效率低下。

友情链接