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

为了按照父任务下面的子任务顺序排列任务，我尝试了以下查询但没有成功：

;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

如果您按照所需输出的结构（如下图所示）的结构，序列**列以及**级别列必须确定排序顺序。

提前致谢。

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

- Yasser Sobhdel

---

很好，你包含了一个[mcve]。然而，当我执行你的代码时，顺序与预期相同（不像你的图片）。（在SQL Server 2014上，但我怀疑版本会有影响） - HoneyBadger

我可以解释为什么你得到了半排序的结果，为了简洁起见，我已经复制了表格并按顺序插入了行，这就是为什么你会得到这样的结果。请在插入表格记录时更改插入行的顺序。谢谢。 - Yasser Sobhdel

序列号3似乎在您的结果中放置正确。级别1/序列号0看起来错位了。 - HoneyBadger

不，这不是预期结果。预期结果是第一张图片。正如您之前提到的，最后4行被放错了位置。 - Yasser Sobhdel

你的意思是输出正确但顺序不对吗？能否请您粘贴正确的输出结果呢？谢谢。请快点。 - KumarHarsh

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 的值相同。这样是正确的吗？

- uzi

---

谢谢你的回答。这产生了正确的结果。我会等待更有效的查询，然后标记为答案。 - Yasser Sobhdel

当然，没问题。由你决定。它的性能差多少？有一个技巧可以加速递归。但这并不保证。你能发布你的最终查询吗？ - uzi

很抱歉，我无法访问主数据，并且为了简洁起见省略了许多细节。让我在主数据库上运行它，然后我会通知您。无论如何感谢您，我对任何性能提升的技巧都非常关注 :) - Yasser Sobhdel

您还没有发布您的查询。因此，我使用一个技巧对我的查询进行了更改。请查看名为cte的CTE末尾。 - uzi

Answer 2

我使用与@Uzi相同的查询，稍作更正。我有与他相同的疑虑。@Yasser应该清楚地展示所需的正确输出，并删除不必要的列。

如果row_number的唯一目的是为了排序记录，那么为什么要将其转换为varchar(8000)？你甚至可以完全避免使用昂贵的Row_number。

利用PK_ID而不是昂贵的row_number，即使在这种情况下PK_ID不按顺序排列也可以。

如果性能是一个大问题，那么用户应该提到3个表中的行数以及在WHERE条件中应用了哪些其他过滤器？

为什么数据类型是uniqueidentifier？如果改为INT，它是否能达到预期的效果？ 阅读此内容

检查此查询：

     WITH cte
    AS (
        SELECT r.PK_ID
            ,r.SourceEntityId
            ,r.TargetEntityId
            ,t.SequenceId,0 k
        FROM #Relation r
        JOIN (
            SELECT id
                ,SequenceId
            FROM #TaskTable1

            UNION ALL

            SELECT id
                ,SequenceId
            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 decimal(3,1))
            --, rn = cast( SequenceId+1 as decimal(3,1))--**
            ,1 step
        FROM cte
        WHERE SourceEntityId = 'DAB00C89-961C-84DD-BB43-CFFD18E63594'

        UNION ALL

        SELECT a.TargetEntityId
            ,b.TargetEntityId
            ,a.SourceEntityId
            ,b.SequenceId

                ,cast((a.rn+(b.SequenceId/10.0)) as  decimal(3,1))
            ,step + 1
        FROM rcte a
        JOIN cte b ON a.TargetEntityId = b.SourceEntityId

        )

    SELECT *

    FROM rcte
    ORDER BY rn
--**
--SELECT *

--FROM rcte
--ORDER BY rn,st

-- 第二次编辑，

我明白无法更改数据库。 在这种情况下，创建索引视图是非常合理的，其中任务表ID是聚集索引。

  select id, SequenceId from #TaskTable1
  union all
  select id, SequenceId from #TaskTable2



     Create nonclustered index NCI_Relation_SourceID on Relation([SourceEntityId])
 Create nonclustered index NCI_Relation_TargetEntityId on Relation([TargetEntityId])

你可以尝试这个组合：

删除PK_ID作为聚集索引，将TargetEntityId作为聚集索引。

你可以尝试在此查询上创建视图。

SELECT r.PK_ID
        ,r.SourceEntityId
        ,r.TargetEntityId
        ,t.SequenceId
    FROM #Relation r
    JOIN (
        SELECT id
            ,SequenceId
        FROM #TaskTable1

        UNION ALL

        SELECT id
            ,SequenceId
        FROM #TaskTable2
        ) t ON r.TargetEntityId = t.id

- KumarHarsh

---

谢谢您的回答，我也可能会回答您的问题：row_number或“x”列仅用于排序。我已经简化了情况，但是在主数据库中，我面临的是guid主键。我正在尝试将此查询用作存储过程，其中硬编码的guid是唯一参数。任务表的大小大约为每个表300,000行，并以每月20,000的速率增加。 - Yasser Sobhdel

感谢您的编辑，但是您的查询并没有提供所需的结果。 - Yasser Sobhdel

你可以在代码中使用 rn 进行注释，并取消注释 "--**" 部分。 - KumarHarsh

Answer 3

通过在CTE表达式中添加名为Hierarchy的新列，并根据此值对结果进行排序，可以解决您的要求。

以下是修改后的CTE查询。

;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
     ,CAST( ROW_NUMBER() OVER (ORDER BY R.SourceEntityId) as varchar(max)) Hierarchy
     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)
    ,CAST(Hierarchy + ':' + CAST(ROW_NUMBER() OVER (ORDER BY R1.SourceEntityId) as varchar(max)) as varchar(max)) as Hierarchy
    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
,Hierarchy
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 Hierarchy 

请注意，我已添加了层次结构列，并使用ROW_NUMBER()函数计算其值，该函数为每个任务创建唯一的整数值。

您可以在参考教程中找到此带有SQL CTE的层次结构查询的实现。希望这能帮到您。

我还在此处添加输出截图，以显示数据如何按层次排序。

尽管子项在父项之后列出，但我发现它与您期望的结果不完全匹配。

- Eralper

---

很抱歉，当按照X列排序时，我无法看到您的输出和我的代码之间的任何区别。我已经运行了您的代码，但是SequenceId为3的行没有被正确地放置。 - Yasser Sobhdel

你是否查看了参考教程，因为那里有类似排序的实现。我可能在层次结构字段中使用了错误的列。 - Eralper

感谢您指出这个教程。我看到了，这是我的问题：每个教程都假设您有一个“序数”或“序列”字段以及分层数据，但我的情况不同。我在另一个表中有关系和“序列”字段，在使用递归CTE时，我达到了需要另一个递归才能完成的结构。由于不支持嵌套CTE，我已经寻求帮助，找到是否有任何类型的查询来解决这个问题？ - Yasser Sobhdel