SQL-对公用表表达式进行排序 [英] SQL - ordering a common table expression
本文介绍了SQL-对公用表表达式进行排序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
使用此示例表:
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返回需要一棵家谱的所有正确人员
I can return all the correct people I needed for a family tree using this 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)将树以正确的顺序放置-这样我就可以得到:
But how can I (just using sql) put the tree in the correct order - so that I get:
-baby
--mother
---mothers mother
---mothers father
--father
---fathers mother
---fathers father
推荐答案
NB:这个答案是在非常肤浅地浏览了《内部Microsoft SQL Server T-SQL查询》一书的层次结构"一章后写的,希望我没有错过任何重要的警告!
NB: This answer was written after an extremely superficial glance at the Hierarchies chapter in the book "Inside Microsoft SQL Server T-SQL Querying" Hopefully I didn't miss any vital caveats!
;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
这篇关于SQL-对公用表表达式进行排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文