SQL Server查询，对多列进行排序

Question

我们有一个嵌套的任务结构，其中每个任务都可以包含其他任务。任务中的任务顺序很重要，由从零开始的 Sequence 字段定义。这是我的表结构:

USE [MyDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Relation](
    [PK_ID] [int] IDENTITY(1,1) NOT NULL,
    [SourceEntityId] [uniqueidentifier] NOT NULL,
    [TargetEntityId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Relation] PRIMARY KEY CLUSTERED 
(
    [PK_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TaskTable1](
    [Id] [uniqueidentifier] NOT NULL,
    [Title] [nvarchar](max) NULL,
    [SequenceId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TaskTable2](
    [Id] [uniqueidentifier] NOT NULL,
    [Title] [nvarchar](max) NULL,
    [SequenceId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Relation] ON 

GO
INSERT [dbo].[Relation] ([PK_ID], [SourceEntityId], [TargetEntityId]) VALUES (1, N'dab00c89-961c-84dd-bb43-cffd18e63594', N'5b266fd1-cbc8-c16a-91c4-5675a35c9ecf')
GO
INSERT [dbo].[Relation] ([PK_ID], [SourceEntityId], [TargetEntityId]) VALUES (2, N'dab00c89-961c-84dd-bb43-cffd18e63594', N'e499ca68-8103-b8ec-06ba-110fa3f6eb5b')
GO
INSERT [dbo].[Relation] ([PK_ID], [SourceEntityId], [TargetEntityId]) VALUES (4, N'dab00c89-961c-84dd-bb43-cffd18e63594', N'645ad2eb-df10-0d5b-0526-408aad45a145')
GO
INSERT [dbo].[Relation] ([PK_ID], [SourceEntityId], [TargetEntityId]) VALUES (5, N'785227d1-393c-ae18-02e5-03ab08d577af', N'5655aeb7-b8b5-dca9-38af-37687c668c14')
GO
INSERT [dbo].[Relation] ([PK_ID], [SourceEntityId], [TargetEntityId]) VALUES (6, N'dab00c89-961c-84dd-bb43-cffd18e63594', N'030cdefc-0e45-01e6-e2a5-a69e303bda4b')
GO
INSERT [dbo].[Relation] ([PK_ID], [SourceEntityId], [TargetEntityId]) VALUES (7, N'dab00c89-961c-84dd-bb43-cffd18e63594', N'0375c7a1-8cc5-a4c8-151c-966e4af83f73')
GO
INSERT [dbo].[Relation] ([PK_ID], [SourceEntityId], [TargetEntityId]) VALUES (8, N'dab00c89-961c-84dd-bb43-cffd18e63594', N'785227d1-393c-ae18-02e5-03ab08d577af')
GO
INSERT [dbo].[Relation] ([PK_ID], [SourceEntityId], [TargetEntityId]) VALUES (9, N'030cdefc-0e45-01e6-e2a5-a69e303bda4b', N'8324bba9-252f-bef8-c018-8b86491e2361')
GO
INSERT [dbo].[Relation] ([PK_ID], [SourceEntityId], [TargetEntityId]) VALUES (10, N'030cdefc-0e45-01e6-e2a5-a69e303bda4b', N'f1cbe8a3-3285-4cf0-096d-aad0327bdb0b')
GO
INSERT [dbo].[Relation] ([PK_ID], [SourceEntityId], [TargetEntityId]) VALUES (11, N'dab00c89-961c-84dd-bb43-cffd18e63594', N'0189f0af-5045-a498-2d70-99187bf3f0ae')
GO
INSERT [dbo].[Relation] ([PK_ID], [SourceEntityId], [TargetEntityId]) VALUES (12, N'785227d1-393c-ae18-02e5-03ab08d577af', N'ffecd091-c17b-ee5f-a64d-54ea9ff65aa9')
GO

SET IDENTITY_INSERT [dbo].[Relation] OFF
GO
INSERT [dbo].[TaskTable1] ([Id], [Title], [SequenceId]) VALUES (N'5b266fd1-cbc8-c16a-91c4-5675a35c9ecf', N'First', 0)
GO
INSERT [dbo].[TaskTable1] ([Id], [Title], [SequenceId]) VALUES (N'e499ca68-8103-b8ec-06ba-110fa3f6eb5b', N'Second', 1)
GO
INSERT [dbo].[TaskTable1] ([Id], [Title], [SequenceId]) VALUES (N'0189f0af-5045-a498-2d70-99187bf3f0ae', N'Fourth', 3)
GO
INSERT [dbo].[TaskTable1] ([Id], [Title], [SequenceId]) VALUES (N'0375c7a1-8cc5-a4c8-151c-966e4af83f73', N'Sixth', 5)
GO
INSERT [dbo].[TaskTable2] ([Id], [Title], [SequenceId]) VALUES (N'030cdefc-0e45-01e6-e2a5-a69e303bda4b', N'Fifth', 4)
GO
INSERT [dbo].[TaskTable2] ([Id], [Title], [SequenceId]) VALUES (N'785227d1-393c-ae18-02e5-03ab08d577af', N'Seventh', 6)
GO
INSERT [dbo].[TaskTable2] ([Id], [Title], [SequenceId]) VALUES (N'645ad2eb-df10-0d5b-0526-408aad45a145', N'Third', 2)
GO
INSERT [dbo].[TaskTable2] ([Id], [Title], [SequenceId]) VALUES (N'8324bba9-252f-bef8-c018-8b86491e2361', N'sub1', 0)
GO
INSERT [dbo].[TaskTable2] ([Id], [Title], [SequenceId]) VALUES (N'f1cbe8a3-3285-4cf0-096d-aad0327bdb0b', N'sub2', 1)
GO
INSERT [dbo].[TaskTable1] ([Id], [Title], [SequenceId]) VALUES (N'ffecd091-c17b-ee5f-a64d-54ea9ff65aa9', N'sub 1', 0)
GO
INSERT [dbo].[TaskTable1] ([Id], [Title], [SequenceId]) VALUES (N'5655aeb7-b8b5-dca9-38af-37687c668c14', N'sub 2', 1)
GO

为了让他们的子任务在他们的 parent 之下按顺序排列，我尝试了以下查询但无济于事:

;With TaskCTE 
AS
(
    select R.SourceEntityId AS ParentTask_Id, R.TargetEntityId AS Task_Id , cast(null as uniqueidentifier) AS ParentTask, 0 AS Level
     , ROW_NUMBER() OVER (ORDER BY (SELECT 100)) / power(10.0,0) as x
     from Relation R
        where (R.SourceEntityId = 'DAB00C89-961C-84DD-BB43-CFFD18E63594')
    UNION ALL
    select R1.SourceEntityId , R1.TargetEntityId, TaskCTE.Task_Id  , Level + 1 
    , x + ROW_NUMBER() OVER (ORDER BY (SELECT 100)) / power(10.0,level+1)
    from Relation R1
        INNER JOIN TaskCTE
            ON R1.SourceEntityId = TaskCTE.Task_Id  
)

select ParentTask_Id, Task_Id, ParentTask, Level 
, COALESCE(TT1.Title, TT2.Title) AS Title
, COALESCE(TT1.SequenceId, TT2.SequenceId) AS SequenceId
, x
from TaskCTE
LEFT OUTER JOIN TaskTable1 TT1 
ON TaskCTE.Task_Id = TT1.Id
LEFT OUTER JOIN TaskTable2 TT2
ON TaskCTE.Task_Id = TT2.Id

order by level , SequenceId

如果您遵循所需输出的结构(如下图所示)，sequence ** 列和 **Level 列必须确定排序顺序。

提前致谢

编辑:我的查询输出是错误的:

Answer 1

如果你的问题是其他表中的序列字段而不是关系表，那么你为什么不在运行递归之前加入它们？但它可能会比您最初的查询慢。这是一个示例

with cte as (
    select 
        r.SourceEntityId, r.TargetEntityId, t.SequenceId, 0 k
    from 
        Relation r
        join (
            select * from TaskTable1
            union all
            select * from TaskTable2
        ) t on r.TargetEntityId = t.id


    ---------------------------------------        
    union all select * from cte where k = 1
    ---------------------------------------
)
, rcte as (
    select
        SourceEntityId, TargetEntityId, ParentTask = cast(null as uniqueidentifier)
        , SequenceId, rn = cast(row_number() over (order by SequenceId) as varchar(8000)), 1 step
    from
        cte
    where
        SourceEntityId = 'DAB00C89-961C-84DD-BB43-CFFD18E63594'
    union all
    select
        a.TargetEntityId, b.TargetEntityId, a.SourceEntityId, b.SequenceId
        , cast(concat(a.rn, '.', row_number() over (partition by b.SourceEntityId order by b.SequenceId)) as varchar(8000))
        , step + 1
    from
        rcte a
        join cte b on a.TargetEntityId = b.SourceEntityId
)
select
    *
from
    rcte
order by rn

我没有包括你的 X 列，我无法得到正在尝试计算的内容。此外，ParentTask 和 ParentTask_Id 的预期输出值相同。应该是这样吗？