按父组和子项排序父子记录 [英] Order parent child records by parent group and children

问题描述

我需要按特定的两个相关列对查询结果进行排序.我的桌子是:

I need sort query results by specific two related columns. My table is:

Row no | Col 1 | Col 2 | Col 3 | Col 4
    1  |   1   |  X    |  1    |  5
    2  |   2   |  Y    |  1    |  6
    3  |   5   |  Z    |  2    |  7
    4  |   6   |  T    |  2    |  0
    5  |   7   |  T    |  3    |  0
    6  |   6   |  W    |  2    |  0

Col 4中的值表示链接到Col 1的子记录.

The values in Col 4 represents the child record linked to Col 1.

因此，对于Row no = 1，下一个子记录是第3行，其中Col 1保存第一行的Col 4值.

So for Row no = 1 the next child record is row 3, where Col 1 holds the value of Col 4 from the first row.

基于Col 1和Col 4之间的链接，第3行的下一个子行是第5行.

The next child row for row 3 is row 5, based on the link between Col 1 and Col 4.

我想返回此结果:

Row no | Col 1 | Col 2 | Col 3 | Col 4
    1  |   1   |  X    |  1    |  5
    3  |   5   |  Z    |  2    |  7
    5  |   7   |  T    |  3    |  0    
    2  |   2   |  Y    |  1    |  6
    4  |   6   |  T    |  2    |  0
    6  |   6   |  W    |  2    |  0 

因此，我希望先显示父行，然后显示子行，然后再移至下一个顶级父行.

So I want the ordering to show a Parent row, followed by it's child rows, before moving on to the next top level Parent row.

推荐答案

您可以通过

You can achieve what you're after with a Recursive CTE to find all the parent records and link them to their child records.

虚拟表设置:

CREATE TABLE #Table1
    (
      [Row no] INT ,
      [Col 1] INT ,
      [Col 2] VARCHAR(1) ,
      [Col 3] INT ,
      [Col 4] INT
    );

INSERT  INTO #Table1
        ( [Row no], [Col 1], [Col 2], [Col 3], [Col 4] )
VALUES  ( 1, 1, 'X', 1, 5 ),
        ( 2, 2, 'Y', 1, 6 ),
        ( 3, 5, 'Z', 2, 7 ),
        ( 4, 6, 'T', 2, 0 ),
        ( 5, 7, 'T', 3, 0 ),
        ( 6, 6, 'W', 2, 0 );

递归CTE:

;WITH    cte
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( ORDER BY t1.[Col 1] ) GroupNo
               FROM     #Table1 t1
               WHERE    t1.[Col 1] NOT IN ( SELECT  [Col 4] FROM #Table1 )
               UNION ALL
               SELECT   t.* ,
                        cte.GroupNo
               FROM     #Table1 t
                        INNER JOIN cte ON cte.[Col 4] = t.[Col 1]
             )
    SELECT  *
    FROM    cte
    ORDER BY cte.GroupNo , cte.[Row no]

DROP TABLE #Table1

这将两个查询与UNION ALL组合在一起.第一个查询查找[Col 1]的值未出现在[Col 4]中的顶级项目:

This combines 2 queries with a UNION ALL. The first query finds the top level items where the value of [Col 1] does not appear in [Col 4]:

WHERE    t1.[Col 1] NOT IN ( SELECT  [Col 4] FROM #Table1 )

第二个查询使用以下JOIN在第一个查询中找到子记录:

The second query finds the child records on the first query with this JOIN:

INNER JOIN cte ON cte.[Col 4] = t.[Col 1]

对于排序，我使用以下代码为第一个查询的结果赋予GroupNo，稍后将其用于对记录进行排序:

For the ordering, I've used the following to give the the results of the first query a GroupNo, which is used later to order the records:

ROW_NUMBER() OVER ( ORDER BY t1.[Col 1] ) GroupNo 

这篇关于按父组和子项排序父子记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！