主页 / dba / 问题 / 292451
Accepted
tucomax
Asked: 2021-05-29 23:59:11 +0800 CST

如何使用层次结构 Postgresql 12 合并一列中的多行？

  • 772

我有一个这样的表来保存 90 个服务的目录：

-------------------------------------------------------------------------------------
|  id  |  service     | subservice      | description                      | cost
-------------------------------------------------------------------------------------
| 2044 |   Tests      |   Tests         | Calcium                          | 50.00
| 1385 |   Cardiology |   Cardioversion | Electric Cardioversion programmed| 200.00
| 7000 |   Cardiology |  Ecocardiography| Chest Ultrasound                 | 100.00
-------------------------------------------------------------------------------------

我需要更改表结构，以使三个级别（服务、子服务和描述）在同一列中具有自己的 id 并指向具有级别编号的新列。即类似这样的东西（请注意，id是我编造的）：

-------------------------------------------------------------------------------------
|  id  | description  | parent_id   |  cost
-------------------------------------------------------------------------------------
| 1    |   Tests      |       NULL  | 0.00
| 2    |   Tests      |         1   | 0.00
| 2044 |   Calcium    |         2   | 50.00
-------------------------------------------------------------------------------------

我正在使用 Postgresql 12。我已经创建了新列 parent_id 并且我正在尝试执行以下操作：

CREATE SEQUENCE seq_parent_id INCREMENT BY 1 START WITH 1 NO CYCLE;

ALTER TABLE catalog
ALTER COLUMN parent_id SET DEFAULT nextval('seq_parent_id')

任何人都可以大致了解如何转换表格的结构吗？

postgresql cte

1 个回答

  1. Best Answer

    它有点冗长，所以步骤是：

    1. 旧表
    create table test (id int, service varchar, subservice varchar, description varchar, cost decimal(10,2));
    1. 新表
    create table newtest(id int, description varchar, parent_id int, cost decimal(10,2));
    1. 如您在问题中提到的那样创建一个序列
    CREATE SEQUENCE seq_parent_id INCREMENT BY 1 START WITH 1 NO CYCLE;
    1. 现在运行下面的查询
    with cte(id, description, parent_id,cost) as 
(select nextval('seq_parent_id'), service, null::bigint,0::decimal  
from test group by 2
),
cte1(id, description, parent_id,cost) as 
(select nextval('seq_parent_id'), subservice, t2.id,0::decimal 
from test t1 
inner join cte t2 on t1.service=t2.description and t2.parent_id is null group by 2,3
)

insert into newtest

select * from cte
union all
select * from cte1
union all
select t1.id,t1.description,t3.id,t1.cost from test t1 
inner join cte t2 on t1.service=t2.description and t2.parent_id is null
inner join cte1 t3 on t1.subservice=t3.description and t3.parent_id=t2.id

    演示

    上述查询的解释在这个小提琴中演示

    注意：在运行查询之前，请确保计数器值不会跨越已经存在的 ID。如果新插入的行数大于现有的最小计数，则max(id)+1在运行此查询之前设置序列的开始。

    • 1

相关问题