更新列的遞回PostgreSQL查詢

您好我正在嘗試使用 SQL 查詢遞回地更新資料庫列。

我有 2 張桌子module和module_dependency.

第一個表module有列（帶有示例資料）：

id, name, state,
1, "website", "installed"
2, "purchase", "installed"
3, "crm", "installed"
4, "sale", "uninstalled"
5, "account", "installed"
6, "website_sale", "installed"
7, "purchase_bonus", "installed"
8, "website_blog", "installed"
9, "sale_discount", "installed"
10, "website_online", "installed"

第二個表module_dependency有列（帶有示例資料）：

 id | dependency_name | module_id 
---- ----------------- -----------
  1 | website_sale    |         1
  2 | sale_bonus      |         4
  3 | website_blog    |         1
  4 | sale_discount   |         4
  5 | website_online  |         1
  6 | crm             |         10
  7 | account         |         3

我需要將網站模塊的狀態更改為“升級”所有它的依賴模塊。

我可以檢查當前模塊的狀態：

SELECT * FROM module WHERE name = 'website' AND state = 'installed';

 id |  name   |   state   
---- --------- -----------
  1 | website | installed

要獲取網站模塊的所有依賴項，我使用查詢：

SELECT m.name, md.dependency_name , md.module_id
FROM module m JOIN module_dependency md ON (m.id = md.module_id)
WHERE m.name = 'website' AND m.state = 'installed';

  name   | dependency_name | module_id 
--------- ----------------- -----------
 website | website_sale    |         1
 website | website_blog    |         1
 website | website_online  |         1

這意味著website_sale、website_blog和website_online取決于 module_id 1 是網站。

主要問題是 website_online 依賴于 crm 和 account 模塊上的 crm。

SELECT m.name, md.dependency_name , md.module_id
FROM module m JOIN module_dependency md ON (m.id = md.module_id)
WHERE m.name = 'website_online' AND m.state = 'installed';

      name      | dependency_name | module_id 
---------------- ----------------- -----------
 website_online | crm             |        10

SELECT m.name, md.dependency_name , md.module_id
FROM module m JOIN module_dependency md ON (m.id = md.module_id)
WHERE m.name = 'crm' AND m.state = 'installed';

 name | dependency_name | module_id 
------ ----------------- -----------
 crm  | account         |         3

我需要遞回地將所有網站依賴項的狀態更改為“升級”。更新的行應如下所示：

id, name, state,
1, "website", "to upgrade"
3, "crm", "to upgrade"
5, "account", "to upgrade"
6, "website_sale", "to upgrade"
8, "website_blog", "to upgrade"
10, "website_online", "to upgrade"

您可以使用測驗資料創建表：

CREATE TABLE module(id integer, name text, state text);
INSERT INTO module VALUES
(1, 'website', 'installed'),
(2, 'purchase', 'installed'),
(3, 'crm', 'installed'),
(4, 'sale', 'uninstalled'),
(5, 'account', 'installed'),
(6, 'website_sale', 'installed'),
(7, 'purchase_bonus', 'installed'),
(8, 'website_blog', 'installed'),
(9, 'sale_discount', 'installed'),
(10, 'website_online', 'installed');


CREATE TABLE module_dependency(id integer, dependency_name text, module_id integer);
INSERT INTO module_dependency VALUES
(1, 'website_sale', 1),
(2, 'sale_bonus', 4),
(3, 'website_blog', 1),
(4, 'sale_discount', 4),
(5, 'website_online', 1),
(6, 'crm', 10),
(7, 'account', 3);

我曾嘗試撰寫遞回查詢，但沒有成功。看起來像無限回圈。如果我可以獲得模塊表的所有模塊 ID，我可以簡單地更新選定的條目。

WITH RECURSIVE modules_to_upgrade AS
(
    SELECT id
    FROM module
    WHERE name = 'website'

        UNION ALL

    SELECT md.module_id
    FROM module_dependency md JOIN modules_to_upgrade mtu on mtu.id = md.module_id
)
select * from modules_to_upgrade;

uj5u.com熱心網友回復：

要將遞回部分鏈接回模塊，您似乎不能使用依賴項 ID。

所以它必須在名字上。

WITH RECURSIVE rcte_modules_to_upgrade AS
(
    SELECT 
      m.id as module_id
    , m.name as module_name
    , md.id as dependency_id
    , md.dependency_name
    , m.id as base_module_id
    , 1 as depth
    FROM module m
    JOIN module_dependency md 
      ON md.module_id = m.id
    WHERE m.name = 'website'

    UNION ALL

    SELECT 
      m.id
    , m.name
    , md.id
    , md.dependency_name
    , rcte.base_module_id
    , rcte.depth   1
    FROM rcte_modules_to_upgrade rcte
    JOIN module m
      ON m.name = rcte.dependency_name
    LEFT JOIN module_dependency md 
      ON md.module_id = m.id
)
select * 
from rcte_modules_to_upgrade;

模塊 ID | 模塊名稱 | 依賴ID | 依賴名 | base_module_id | 深度
--------: | :------------- | ------------: | :---------------- | -------------: | ----:
        1 | 網站 | 1 | 網站銷售| 1 | 1
        1 | 網站 | 3 | 網站博客 | 1 | 1
        1 | 網站 | 5 | 網站_在線 | 1 | 1
        6 | 網站銷售|          空| 空            | 1 | 2
        8 | 網站博客 |          空| 空            | 1 | 2
       10 | 網站_在線 | 6 | 客戶關系管理 | 1 | 2
        3 | 客戶關系管理 | 7 | 帳戶 | 1 | 3
        5 | 帳戶 |          空| 空            | 1 | 4

WITH RECURSIVE rcte_modules_to_upgrade AS
(
    SELECT 
      m.id as module_id
    , md.dependency_name
    FROM module m
    JOIN module_dependency md 
      ON md.module_id = m.id
    WHERE m.name = 'website'

    UNION ALL

    SELECT 
      m.id
    , md.dependency_name
    FROM rcte_modules_to_upgrade rcte
    JOIN module m
      ON m.name = rcte.dependency_name
    LEFT JOIN module_dependency md 
      ON md.module_id = m.id
)
update module
set state = 'to upgrade'
where id in (select distinct module_id 
             from rcte_modules_to_upgrade);

6 行受影響

select * from module order by id;

編號 | 姓名 | 狀態      
-: | :------------- | :----------
 1 | 網站 | 升級
 2 | 購買 | 已安裝  
 3 | 客戶關系管理 | 升級
 4 | 出售| 已卸載
 5 | 帳戶 | 升級
 6 | 網站銷售| 升級
 7 | 購買獎勵 | 已安裝  
 8 | 網站博客 | 升級
 9 | 銷售折扣 | 已安裝  
10 | 網站_在線 | 升級

db<>在這里擺弄

標籤：

上一篇：postgresql訂閱不適用于公共以外的模式

下一篇：Postgresql陣列到json