主页 / dba / 问题 / 290684
Accepted
alexandre9865
Asked: 2021-04-29 17:00:38 +0800 CST

根据 SQLite 中的 list_order 对具有路径索引的嵌套表进行排序

  • 772

我有一个具有这种嵌套结构的 SQL 表：

CREATE TABLE items (`id` INTEGER, `item_name` VARCHAR(6), `parent_id` INTEGER, `list_order` INTEGER);

我的 SQLite 版本不支持窗口函数，目前我在链接SQLite 查询中的查询返回结果如下，但它不是根据 list_order 排序的，另一个细节是 list_order 列可能所有值都为零。

   id      name_item   parent_id  level  path_index list_order
---------------------------------------------------------------
   1        Pois           0          0     1           4
   2        Então          0          0     2           5
   3        Teste          0          0     3           3
   11       Teste 3        3          1     3.1         2
   12       Teste 2        3          1     3.2         0
   13       Teste 1        3          1     3.3         1
   4        Fundo          0          0     4           2
   7        Profundo       4          1     4.1         1
   8        Dhdhd          4          1     4.2         0
   9        Gagagaga       8          2     4.2.1       1
   10       Fsfsfsfs       8          2     4.2.2       0
   5        Profundo       0          0     5           1
   6        Gigante        0          0     6           6
   14       Teste          0          0     7           0

我希望 path_index 列遵循 list_order 和 level 列，如下所示：

   id      name_item   parent_id  level  path_index list_order
---------------------------------------------------------------
   14       Teste          0          0     1           0
   5        Profundo       0          0     2           1
   4        Fundo          0          0     3           2
   8        Dhdhd          4          1     3.1         0
   10       Fsfsfsfs       8          2     3.1.1       0
   9        Gagagaga       8          2     3.1.2       1
   7        Profundo       4          1     3.2         1
   3        Teste          0          0     4           3
   12       Teste 2        3          1     4.1         0
   13       Teste 1        3          1     4.2         1
   11       Teste 3        3          1     4.3         2
   1        Pois           0          0     5           4
   2        Então          0          0     6           5
   6        Gigante        0          0     7           6

如何对齐根据 list_order 和 level 列排序的 path_index？

sqlite order-by

1 个回答

  1. Best Answer

    下一个查询可以执行所需的排序：

    WITH
cte AS ( SELECT *, CAST(list_order AS TEXT) as ord
         FROM items
         WHERE parent_id = 0
       UNION ALL
         SELECT items.*, cte.ord || '.' || items.list_order
         FROM items
         JOIN cte ON cte.id = items.parent_id )
SELECT * 
FROM cte
ORDER BY ord

    https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=a7957edeabb5a9a1c9dff2d2e92ffe0c

    如果存在list_order上述值，9则必须对其值应用零填充表达式。例如

    WITH
cte AS ( SELECT *, printf('%04d', list_order) as ord
         FROM items
         WHERE parent_id = 0
       UNION ALL
         SELECT items.*, cte.ord || printf('%04d', items.list_order)
         FROM items
         JOIN cte ON cte.id = items.parent_id )
SELECT * 
FROM cte
ORDER BY ord

    https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=52d6d43db054cb64754c37d3820c0b1e

    您有什么技巧可以使零填充动态化吗？– 亚历山大9865

    WITH
printf_format AS ( SELECT '%0' || LENGTH(MAX(list_order)) || 'd' AS format
                   FROM items ),
cte AS ( SELECT items.*, printf(printf_format.format, list_order) as ord
         FROM items
         CROSS JOIN printf_format
         WHERE parent_id = 0
       UNION ALL
         SELECT items.*, cte.ord || printf(printf_format.format, items.list_order)
         FROM items
         JOIN cte ON cte.id = items.parent_id
         CROSS JOIN printf_format )
SELECT * 
FROM cte
ORDER BY ord

    https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=728695f41db0913bcde76370f70c885a

    • 3

相关问题