在CASE上使用多個JOIN進行更新

我正在Postgres 12UPDATE中撰寫一個sql 查詢。

查詢非常復雜，它需要包含JOIN和CASE才能作業。到目前為止，我無法理解它。我需要更新approval_statuscomment_response 表上的一列。

該team_member_manager表指示誰需要批準評論并且他們只是有效的評論（即使還有更多其他人在post_comment_response_approval）

我需要4個案例：

• 如果需要提交回復的每個人都提交了批準，那么狀態應該是批準。

• 如果沒有關聯的 team_member_manager 記錄，則狀態也應該是批準的。

• 如果有人拒絕了它，則應拒絕該狀態。

• 否則，狀態應為待處理。

這是具有正確值的表結構。

post_comment_response桌子：

post_comment桌子：

post桌子：

team_member_manager桌子：

team_member桌子：

post_comment_response_approval桌子：

注意：我正在定義經理和誰是管理成員，我想通過加入 post_comment → postJOIN managing_team_member_id獲得具有已定義值 (91) 值的成員managed_team_member_id

我的 sql 陳述句和JOINs 正在正常作業，但我陷入了如何使用CASEs 正確實作它們的問題上。有人可以幫忙嗎？

UPDATE post_comment_response pcr
SET
approval_status = CASE WHEN 'first case'= 'approved'
                  WHEN 'second case' = 'approved',
                  WHEN 'third case' = 'rejected'
                  ELSE 'pending'
JOIN post_comment pc ON pc.id = 1173
JOIN post p ON p.id = pc.post_id
JOIN team_member_manager tmm ON tmm.managed_team_member_id = p.team_member_id
JOIN team_member mgr ON mgr.id = tmm.managing_team_member_id
WHERE mgr.team_id = 91;

uj5u.com熱心網友回復：

“如果來自 team_member_manager_approval 的所有相關行都被批準，則批準，如果其中一個為假，則拒絕”

要獲得此結果，您需要使用bool_and應用于approved表列的聚合函式，post_comment_response_approval同時對上的行進行分組post_comment_response.id

“如果沒有行也批準”

要獲得此結果，您需要LEFT JOIN將表post_comment_response_approval與其他表匹配，以便如果沒有行匹配，則將為來自該表的列填充 NULL 值。

然后您可以使用CASE WHEN ... THEN ... END陳述句將布爾結果bool_and轉換為文本：true => 'approve', false => 'reject'。如果表中不存在任何行，post_comment_response_approval則將bool_and(...)回傳NULL并將COALESCE(bool_and(...), true)回傳true以便選擇“批準”值。

由于不能在 a 的SET子句中直接使用聚合函式UPDATE，因此必須在 a 中實作查詢cte，并cte在UPDATE.

從您的資料樣本開始，并嘗試遵循您的業務邏輯，更新表中對應的解決方案approval_status是：post_comment_responsepost_comment = 1173team_member.team_id = 91

WITH list AS (
SELECT 
       pcr.id, pcr.post_comment_id, pcr.comment
     , CASE WHEN COALESCE(bool_and(pcra.approved), true) THEN 'approve' WHEN NOT bool_and(pcra.approved) THEN 'reject' ELSE 'pending' END AS approval_status
  FROM post_comment_response pcr
  LEFT JOIN post_comment_response_approval pcra ON pcra.post_comment_response_id = pcr.id
  JOIN team_member_manager tmm ON tmm.managed_team_member_id = pcra.team_member_id
  JOIN team_member tm ON tm.id = tmm.managing_team_member_id
 WHERE pcr.post_comment_id = 1173
   AND tm.team_id = 91
  GROUP BY pcr.id, pcr.post_comment_id, pcr.comment
)
UPDATE post_comment_response pcr
   SET approval_status = l.approval_status
  FROM list l
 WHERE pcr.id = l.id ;

post_comment_response但您也可以使用以下查詢一次更新表的所有行：

WITH list AS (
SELECT 
       pcr.id, pcr.post_comment_id, pcr.comment
     , CASE WHEN COALESCE(bool_and(pcra.approved), true) THEN 'approve' WHEN NOT bool_and(pcra.approved) THEN 'reject' ELSE 'pending' END AS approval_status
  FROM post_comment_response pcr
  LEFT JOIN post_comment_response_approval pcra ON pcra.post_comment_response_id = pcr.id
  JOIN team_member_manager tmm ON tmm.managed_team_member_id = pcra.team_member_id
  JOIN team_member tm ON tm.id = tmm.managing_team_member_id
  JOIN post_comment pc ON pc.id = pcr.post_comment_id
  JOIN post p ON p.id = pc.post_id
  JOIN team_member_manager tmm2 ON tmm2.managed_team_member_id = p.team_member_id AND tmm2.managing_team_member_id = tm.id
 GROUP BY pcr.id, pcr.post_comment_id, pcr.comment
)
UPDATE post_comment_response pcr
   SET approval_status = l.approval_status
  FROM list l
 WHERE pcr.id = l.id ;

在dbfiddle中查看測驗結果

標籤：sqlPostgreSQL加入sql更新postgres-12

上一篇：基于postgresql中已有的其他表創建一個新表

下一篇：React-當父級在功能組件中更改其狀態時重置子級狀態