时间戳

首页 > 解决方案 > 在 Postgres 中对子元素进行动态排序的查询

postgresql - 在 Postgres 中对子元素进行动态排序的查询

问题描述

我有一个如下所示的数据集（PostgresQL 版本 11），我想在其中添加一个带有小数点的排序列，以便我可以轻松地在我的可视化中映射父子树。

我们可以做到这一点的任何方法或查询，尝试使用行号和排名，但这无济于事。

数据集：

Parent      Child
Vitamine    Vitamine
Vitamin D   Vitamin D
Vitamin D   Vitamin D3
Vitamin D   Vitamin D K2
Vitamin D   D3
Vitamin D   Vitamin D 1000
Vitamin D   Vitamin D 10000
Vitamin D   Vitamin D 20000
Vitamin K2  Vitamin K2
Vitamin K2  Vitamin K
Vitamin K2  Vitamin K2 Mk7
Vitamin C   Vitamin C

预期输出

  Parent      Child             Sort_order
    Vitamine    Vitamine           1
    Vitamin D   Vitamin D          2
    Vitamin D   Vitamin D3         2.1
    Vitamin D   Vitamin D K2       2.2
    Vitamin D   D3                 2.3
    Vitamin D   Vitamin D 1000     2.4
    Vitamin D   Vitamin D 10000    2.5
    Vitamin D   Vitamin D 20000    2.6
    Vitamin K2  Vitamin K2         3
    Vitamin K2  Vitamin K          3.1
    Vitamin K2  Vitamin K2 Mk7     3.2
    Vitamin C   Vitamin C          4

标签： postgresql

解决方案

考虑以下表模式：

create table vitamins (
  id serial primary key,
  parent_id integer,
  name varchar(64),
  index smallint
);

使用以下数据：

insert into vitamins (id, parent_id, name, index) values
  (1, null, 'Vitamine', 1),
  (2, null, 'Vitamin D', 2),
  (3, 2, 'Vitamin D3', 1),
  (4, 2, 'Vitamin D K2', 2),
  (5, 2, 'D3', 3),
  (6, 2, 'Vitamin D 1000', 4),
  (7, 2, 'Vitamin D 10000', 5),
  (8, 2, 'Vitamin D 20000', 6),
  (9, null, 'Vitamin K2', 3),
  (10, 9, 'Vitamin K', 1),
  (11, 9, 'Vitamin K2 Mk7', 2),
  (12, null, 'Vitamin C', 4)
;

我们可以通过以下查询获得您想要的预期输出：

WITH RECURSIVE recursive_vitamins(id, parent_id, index, parent_name, name, ref, sort) AS (
    SELECT c.id, c.parent_id, c.index, c.name, c.name, c.index::text, lpad(c.index::text, 3, '0')
    FROM vitamins c
    WHERE parent_id is null
    UNION ALL
    SELECT c.id, c.parent_id, c.index, rc.parent_name, c.name,
           CASE WHEN rc.ref = '' THEN c.index::text
                ELSE (rc.ref || '.' || c.index)
               END, (rc.sort || '.' || lpad(c.index::text, 3, '0'))
    FROM vitamins c, recursive_vitamins rc
    WHERE c.parent_id = rc.id
)

SELECT
    parent_name AS parent,
    name AS child,
    ref AS sort_order
FROM recursive_vitamins
ORDER BY sort;

结果：

parent      child         sort_order
Vitamine    Vitamine      1
Vitamin D   Vitamin D     2
Vitamin D   Vitamin D3    2.1
Vitamin D   Vitamin D K2  2.2
Vitamin D   D3            2.3
...

（整个事情的sqlfiddle）

等等，这里发生了什么？

因此，我们创建了一个递归 CTE，允许查询引用它自己的输出。在WITH查询中，我们首先选择所有父母（parent_id is null）然后我们选择所有引用的孩子parent_id。

该ref字段是父索引和子索引的串联。该sort字段也指代index，但它将with填充到总长度为 3。您可以将其省略，而只是，但如果您有>= ，则顺序将变为我猜您不想要的顺序.index0order by refindex101, 10, 10.1, 2

推荐阅读