SQL - 按顺序选择父子记录

Question

我有一个表格，其中既包含问题ID，又包含父问题IDs，如下所示：

QUES_ID | Ques_Txt  | Parent_Ques_Id
--------+-----------+---------+---------------
     97  |  X       | NULL
     101 |  Y       | NULL
     115 |  Z       | NULL
     198 |  A       | 97
     302 |  B       | 97
     151 |  C       | 101
     91  |  D       | 115
   1110  |  E       | 115
   1111  |  F       | 115

现在我想按照以下顺序获取结果集：

QUES_ID | Ques_Txt  | Parent_Ques_Id
--------+-----------+---------+---------------
     97  |  X       | NULL
     198 |  A       | 97
     302 |  B       | 97
     101 |  Y       | NULL
     151 |  C       | 101
     115 |  Z       | NULL         
     91  |  D       | 115
   1110  |  E       | 115
   1111  |  F       | 115

在子问题下方列出父问题，我已经研究了类似的查询，但我发现所有的教程都是关于如何使用左连接或CTE来显示父子记录，而不是像上面表格中所示的那样按我的要求排序。请有人能帮忙吗？我还在学习中。

- James

---

FYI，这是可行的，但这是一个难题。 - RBarryYoung

两个问题，1）SQL Server的版本是什么？2）具有相同父级的兄弟/子项的最大数量是多少？ - RBarryYoung

我正在使用SQL 2012，根据我得到的数据，一个给定的父节点最多可以有4个子节点。 - James

有多少级子代可以存在？（例如，可以有孙子代吗？） - Martin Navarro

不，不会有任何孙子，但是一个父母最多可以有四个孩子。 - James

Answer 1

您可以使用 coalesce 技巧来实现此操作：

select *
from t
order by coalesce(parent_ques_id, ques_id), parent_ques_id, ques_id

排序逻辑如下：

1. 将所有父子关系一个接一个地进行排序（97、101、115）coalesce(parent_ques_id, ques_id) - 如果一个父问题没有子节点，则赋予该父问题自身的id，以便与其子问题一起排序
2. 使父问题在其组的顶部显示 parent_ques_id - 在SQL Server中，默认情况下，空值会按升序排在非空值之前
3. 按其id对子问题进行排序 ques_id

- Kamil Gosciminski

---

如果ques_id按顺序排列，那么这将起作用，但它们并没有。例如，91会在其父级115之前返回... - sgeddes

@sgeddes 请查看http://rextester.com/NLKY17569。最好针对它应该使用的数据库进行测试，因为Postgres和SQL Server在order by子句中具有不同的默认null处理方式。 - Kamil Gosciminski

@RBarryYoung 我以前遇到过这个问题，但是91会排在其父级（id更高的115）之前，sdeddes指出了这一点（感谢），因此需要按照下一列进行排序。 - Kamil Gosciminski

@KamilG. -- 干得好！我应该意识到我在测试错误的数据库...点赞！ - sgeddes

@KamilG。没错，楼主在我开始写答案之后才在评论中添加了这个信息。 - RBarryYoung

Answer 2

使用 order by 子句：

order by (case when Parent_Ques_Id is null 
               then QUES_ID else Parent_Ques_Id end), Parent_Ques_Id, QUES_ID;

- Yogesh Sharma

Answer 3

好的，这是我会做的方式，这个版本可以处理多达99个兄弟姐妹。就像我说的，它很复杂。

;WITH cte AS
(
    SELECT
        QUES_ID,
        Quest_Txt,
        Parent_Quest_Id,
        --(assumes max 9 siblings)
        RIGHT('0'+CAST(ROW_NUMBER()OVER(PARTITION BY Parent_Quest_Id ORDER BY Quest_Txt) AS VARCHAR(MAX)), 1) AS [path],
        0 AS level
    FROM Questions
    WHERE Parent_Quest_Id = 0

  UNION ALL
    SELECT
        t.QUES_ID,
        t.Quest_Txt,
        t.Parent_Quest_Id,
        [path] +'-'
        --(assumes max 99 siblings)
        + RIGHT('00'+CAST(ROW_NUMBER()OVER(PARTITION BY t.Parent_Quest_Id ORDER BY t.Quest_Txt) AS VARCHAR(max)), 2),
        level+1
    FROM cte
    JOIN Questions t ON cte.QUES_ID = t.Parent_Quest_Id
)
SELECT
    QUES_ID,
    Quest_Txt,
    Parent_Quest_Id,
    [path]
FROM cte
ORDER BY path

- RBarryYoung

---

哎呀，错了，没有祖父母/孙子女。 如果这仍然是真的，则@KamilG的解决方案更简单且更可取。 - RBarryYoung