更改递归CTE生成的分层结果的顺序? [英] Altering the order of a hierarchical result generated by a Recursive CTE?

问题描述

我正在使用MySQL，我想知道是否可以更改递归CTE生成的结果的顺序.

I am using MySQL and I am wondering if it is possible to alter the order of the result generated by recursive CTE.

我的表格"具有以下列:

My Table has these columns:

|----------|----------|----------|----------|
|   ID     | parentID |  title   |   Sort   |
|----------|----------|----------|----------|
|    1     |    null  |  Maria   |     1    |
|    2     |    1     |  John    |     2    |
|    3     |    2     |  Maria   |     3    |
|    4     |    1     |  Anthony |     1    |
|    5     |    4     |  XXX     |     1    |
|    6     |    4     |  ...     |     2    |
|    7     |    2     |  ...     |     2    |
|    8     |    2     |  ...     |     1    |
|    9     |    1     |  ...     |     3    |

我使用以下查询(我们不考虑排序列)

I use the following query (We don't take into account the sort column)

   WITH RECURSIVE cte AS
    (
      SELECT ID, parentID, title, 0 AS depth, CAST(ID AS CHAR(200)) AS path
        FROM categories WHERE ID = 1
      UNION ALL
      SELECT c.ID, c.parentID, c.title, cte.depth + 1, CONCAT(cte.path, ',', c.ID) 
        FROM categories c 
        JOIN cte ON cte.parentID = c.ID
        WHERE FIND_IN_SET(c.ID, cte.path)=0
    )
    SELECT * FROM cte ORDER BY cte.path

接下来是我们从上述查询中获得的分层结果(以ID为单位).我们完全忽略了排序列.

Next is the hierarchical result (in IDs) that we get from the above query. We completely ignore the sort column.

Hierarchy Depth
1   2   3
|   |   |
IDs
1
    2
        3
        7
        8
    4
        5
        6
    9

我想要的是一个查询，该查询考虑了sort列并创建以下顺序.从您看到的ID来看，具有4,2,9的ID分别具有排序编号1,2,3，并且在深度级别2的结果中考虑了该顺序，并且在所有深度级别中也是如此.

What I desire is a query that takes account the sort column and creates the following order. From what you see IDs with 4,2,9 have sort numbers 1,2,3 respectively and that order is taken into account in the result in depth level 2 and similarly in all Depth levels.

Hierarchy Depth
1   2   3
|   |   |
IDs
1
    4
        5
        6
    2
        8
        7
        3
    9

正在寻找对我的查询的修改以实现上述结果.

Looking for an edit to my query to achieve the above result.

推荐答案

我认为您可以根据排序优先级构建排序路径.不幸的是，这些似乎在不同的行上重复出现，因此我还将包括原始ID:

I think you can construct the sorting path based on the sort priorities. Unfortunately, these seem to be repeated on different rows, so I am also going to include the original id:

WITH RECURSIVE cte AS (
      SELECT ID, parentID, title, 0 AS depth, CAST(ID AS CHAR(200)) AS path,
             CONCAT(sort, '-', id) as sort_path
      FROM categories
      WHERE ID = 2
      UNION ALL
      SELECT c.ID, c.parentID, c.title, cte.depth + 1, CONCAT(cte.path, ',', c.ID) ,
             CONCAT(cte.sort_path, c.sort, '-', c.id, ',') as sort_path
      FROM categories c JOIN
           cte
           ON cte.parentID = c.ID
      WHERE FIND_IN_SET(c.ID, cte.path) = 0
    )
SELECT *
FROM cte
ORDER BY sort_path;

此处是db小提琴.对于您的样本数据，这仅深入了一层，因此实际上并没有显示它是否有效.此外，这还假定id和排序优先级永远不会超过一个字符-顺便说一句，您的查询也是如此.

Here is a db<>fiddle. With your sample data, this only goes one level deep, so it really doesn't show whether or not this works. Also, this assumes that ids and sorting priorities are never more than one character -- as does your query, by the way.

这篇关于更改递归CTE生成的分层结果的顺序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！