如何对带有子组的有序列表进行 SQL 查询？

Question

我有一个具有这种结构的表，

 id integer
  parent_id integer
  order_n integer
  info text

每行可以有一个父行（parent_id，如果没有父行则为 null）和插入顺序（order_n）。每次插入行时，order_n 字段都会设置相关的“在其父级内部”。因此，第一级的两行将是 order_n = 1 和 order_b = 2。但是第 1 行“内部”的新行将是 order_n = 1

示例

  id    parent_id   order_n   info
  1     null        1         "Beatles"
  2     null        2         "Stones"
  3     1           1          "Paul"
  4     1           2          "John"
  5     2           1          "Mick"
  6     2           2          "Keith"

子级别是无限的。

我想做的事情（我惨败了）是进行一个查询，检索任何级别（包括第一级别）的所有行，并根据他的 order_n 属性对其进行排序，但对嵌套行进行分组。例如，在前面的示例中，我们需要以这种方式检索结果，

1     null        1         "Beatles"
3     1           1          "Paul"
4     1           2          "John"
2     null        2         "Stones"
5     2           1          "Mick"
6     2           2          "Keith"

我正在尝试，但我对 SQL 知之甚少，我会提前感谢您所有明智的建议。

我正在使用 MySQL，但理想的是尝试一些“sql 标准”

内部级别是无限的。

Answer 1

在最后一行“内部级别是无限的”之前，编写查询并不困难。对于您需要的每个级别，您都需要将表格连接一次。如果您预定义了最多 5 个级别，则可以将表与其自身连接 5 次（左连接）来实现此目的。

非常伪代码：

Select whatever
from mytable my1
left join mytable my2 on my1.id = my2.parent_id
where whatever
order by case when parent_id is null then id else parent_id end, 
case when parent_id  is null then 0 else order_n end

order by 子句中的 case 语句旨在识别顶级父记录并将其与其余记录分组。

想要更多级别吗？扩展连接语句：
在 my2.id = my3.parent_id 上左连接 mytable my3
在 my3.id = my4.parent_id 上左连接 mytable my4
left join mytable my5 on my4.id = my5.parent_id

没有“最大”级别数，根据 Matthew PK 的动态 SQL 是（据我所知）您唯一的资源。

my1、my2、my3 等可能也不是最简单的别名命名约定，请选择您可以遵循的名称。

Answer 2

这将是动态 SQL，并且理解起来会有点复杂。

我们需要更多的细节来确定如何提供帮助，但它将从以下内容开始：

您需要确定查询的行深度，否则您将面临无限循环的风险。

确定每行中需要哪些列

基本上，您将在存储过程中创建一个 varchar，该存储过程将根据查询的需要而增长。

您需要将一个变量设置为该查询中的最高 n 阶，然后循环遍历，为每个嵌套表添加列、联接、顺序、组和（最重要的是！）别名。

这是一些伪代码：

int maxval = 0
select maxval = max(n-level) from table where ...
string newalias = ""
string oldalias = newguid
int cnt = 0
int parentid = NULL

string selectstatement = "select "
string joinstatement = "from table as" oldalias
string orderstatement = "order by"

while cnt < maxval
    newalias = newguid

    selectstatement = selectstatement + "," + newalias + "." + columnnames
    joinstatement = joinstatement + " JOIN table as " + newalias + "on " + newalias + ".columnname = " + oldalias + ".columname"
    orderstatement = orderstatement + "," + newalias + ".columnname"
maxval = maxval + 1
oldalias= newalias
loop