代码之家 › 专栏 › 技术社区 › simon831

SQL-对公用表表达式排序

hierarchy tsql sql-server sql

simon831 · 技术社区 · 14 年前

使用此示例表:

drop table Population
CREATE TABLE [dbo].[Population](
    [PersonId] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [MotherId] [int] NULL,
    [FatherId] [int] NULL
) ON [PRIMARY]
insert Population (PersonId, [Name], MotherId, FatherId) values (1, 'Baby', 2, 3)
insert Population (PersonId, [Name], MotherId, FatherId) values (2, 'Mother', 4, 5)
insert Population (PersonId, [Name], MotherId, FatherId) values (3, 'Father', 6, 7)
insert Population (PersonId, [Name], MotherId, FatherId) values (4, 'Mothers Mother', 8, 9)
insert Population (PersonId, [Name], MotherId, FatherId) values (5, 'Mothers Father', 99, 99)
insert Population (PersonId, [Name], MotherId, FatherId) values (6, 'Fathers Mother', 99, 99)
insert Population (PersonId, [Name], MotherId, FatherId) values (7, 'Father Father', 99, 99)
insert Population (PersonId, [Name], MotherId, FatherId) values (8, 'Mothers GrandMother', 99, 99)
insert Population (PersonId, [Name], MotherId, FatherId) values (9, 'Mothers GrandFather', 99, 99)

我可以使用此SQL返回家庭树所需的所有正确人员

;WITH FamilyTree
AS
(
    SELECT *, CAST(NULL AS VARCHAR(50)) AS childName, 0 AS Generation
    FROM Population
    WHERE [PersonId] = '1'

    UNION ALL

    SELECT Fam.*, FamilyTree.[Name] AS childName, Generation + 1
    FROM Population AS Fam
    INNER JOIN FamilyTree
    ON Fam.[PersonId] = FamilyTree.[motherId]

    UNION ALL

    SELECT Fam.*, FamilyTree.[Name] AS childName, Generation + 1
    FROM Population AS Fam
    INNER JOIN FamilyTree
    ON Fam.[PersonId] = FamilyTree.[fatherId]

)

SELECT childName, space(generation*2)+name, generation FROM FamilyTree

它给了我:

-baby
--mother
--father
---fathers mother
---fathers father
---mothers mother
---mothers father

但我如何(仅使用SQL)将树按正确的顺序排列-以便获得:

-baby
--mother
---mothers mother
---mothers father
--father
---fathers mother
---fathers father

2 回复 | 直到 14 年前

Martin Smith 14 年前

注意:这个答案是在“在Microsoft SQL Server T-SQL查询中”这本书的“层次结构”一章中非常肤浅的一瞥之后写的,希望我没有错过任何重要的警告!

;WITH FamilyTree
AS
(
    SELECT *, CAST(NULL AS VARCHAR(50)) AS childName, 0 AS Generation, '.' + CAST([PersonId] AS VARCHAR(max)) + '.' as Path 
    FROM Population
    WHERE [PersonId] = '1'

    UNION ALL

    SELECT Fam.*, FamilyTree.[Name] AS childName, Generation + 1, Path + '0.' + CAST(Fam.[PersonId] AS VARCHAR(max)) + '.' as Path 
    FROM Population AS Fam
    INNER JOIN FamilyTree
    ON Fam.[PersonId] = FamilyTree.[MotherId]

    UNION ALL

    SELECT Fam.*, FamilyTree.[Name] AS childName, Generation + 1, Path + '1.' + CAST(Fam.[PersonId] AS VARCHAR(max)) + '.' as Path 
    FROM Population AS Fam
    INNER JOIN FamilyTree
    ON Fam.[PersonId] = FamilyTree.[FatherId]

)

SELECT childName, space(Generation*2)+Name, Generation, Path
FROM FamilyTree
ORDER BY Path

Damien_The_Unbeliever 14 年前

;WITH FamilyTree
AS
(
    SELECT *, CAST(NULL AS VARCHAR(50)) AS childName, 0 AS Generation, CAST(RIGHT('0000000000' + CAST(PersonId as varchar(10)),10) as varchar(max)) as Descendents
    FROM Population
    WHERE [PersonId] = '1'

    UNION ALL

    SELECT Fam.*, FamilyTree.[Name] AS childName, Generation + 1,FamilyTree.Descendents + '|' + RIGHT('0000000000' + CAST(Fam.PersonId as varchar(10)),10)
    FROM Population AS Fam
    INNER JOIN FamilyTree
    ON Fam.[PersonId] = FamilyTree.[motherId]

    UNION ALL

    SELECT Fam.*, FamilyTree.[Name] AS childName, Generation + 1,FamilyTree.Descendents + '|' + RIGHT('0000000000' + CAST(Fam.PersonId as varchar(10)),10)
    FROM Population AS Fam
    INNER JOIN FamilyTree
    ON Fam.[PersonId] = FamilyTree.[fatherId]

)

SELECT childName, space(generation*2)+name, generation FROM FamilyTree order by Descendents

基本上,您构建了一个大的排序键,确保在字符串的同一位置上相同级别的所有ID都不同。