根據 SQLite 中的 list_order 對具有路徑索引的嵌套表進行排序

我有一個具有這種嵌套結構的 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？

下一個查詢可以執行所需的排序：

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

引用自：https://dba.stackexchange.com/questions/290684