tucomax's questions

我有一张这样的桌子：

-------------------------------------------------
|  id  | description         | parent_id   |  cost
--------------------------------------------------
| 1    |  Radiology         |       NULL  | 0.00
| 2    |  Lab Tests         |       NULL  | 0.00
| 3    |  Normal Radiology  |         1   | 0.00
| 4    |  Resonance         |         1   | 100.00
| 1100 |  Cerebral Resonance|         4   | 200.00
| 1900 |  Blood Tests       |         2   | 10.00
| 2044 |  Calcium           |         2   | 50.00

---------------------------------------------------

我需要生成这种输出：

Radiology
   -->Normal Radiology
   -->Resonance
      -->Cerebral Resonance with contrast
Lab Test
    --> Blood Test
    --> Calcium

我正在研究 PostgreSQL。我一直在尝试使用递归 CTE，但无法生成我喜欢的内容：

WITH RECURSIVE hierarchy AS (
    SELECT  id, CAST(description AS TEXT) AS parent_list
    FROM    orders
    WHERE   parent_id is null
      UNION
    SELECT  c.id,
            CAST(c2.parent_list || ' --> ' || c.description as text) as parent_list
    FROM orders c
    INNER JOIN hierarchy c2 ON c.parent_id = c2.id )

SELECT  id, parent_list
FROM     hierarchy
GROUP BY id, parent_list
ORDER BY parent_list;

该递归 CTE 会产生以下不受欢迎的输出：

Radiology
Radiology--> Normal Radiology
Radiology--> Resonance
Radiology--> Resonance --> Cerebral Resonance with contrast
Lab Test
Lab Test --> Blood Test
Lab Test --> Calcium

我该怎么做？

我有一个这样的表来保存 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')

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

我正在尝试使用表中的两个参数 year 和 code 执行一个函数，以返回给定代码和年份的计数。表是这样的：

CREATE TABLE orders (
    id INT NOT NULL, 
    code INT NOT NULL, 
    service_id INT NOT NULL, 
    status CHARACTER VARYING(50), 
    creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status_date TIMESTAMP,
    created_user INT
    );

我能够使用下面的函数返回计数，但是当我为年份和没有计数的代码提供值时，它返回 NULL 但应该返回零，我该怎么做？：

CREATE OR REPLACE FUNCTION yearly_orders
    (year INT, order_cat orders.code%type) RETURNS INT AS $$
    
DECLARE
    total INT DEFAULT 0;
    
BEGIN
    SELECT COUNT(orders.code) INTO total
    FROM orders
    WHERE status = 'Requested' AND EXTRACT(YEAR FROM creation) = year::INT AND orders.code = order_cat
    GROUP BY year, orders.code;
    RETURN total;
END;
$$LANGUAGE plpgsql;

数据类似于：

INSERT INTO orders 
(id, code, service_id, status, creation, status_date, created_user) 
VALUES 
(100,2394,558151,'Requested','2019-06-16 11:12','2019-06-18 14:08',1);