在SQL中，如何將這些表連接到另一個表中？

抱歉，我正在為應該很簡單的事情而苦苦掙扎。

我有表“Risks_For_Task_1”：

 -------------- ------------- -------------- 
| RiskName     | Mitigation  | RiskLevel    |
 -------------- ------------- -------------- 
| Risk A       | Mitigate#1  | Medium       |
| Risk B       | Mitigate#2  | Low          |
| Risk C       | Mitigate#3  | High         |
 -------------- ------------- -------------- 

還有一個表“Risks_For_Task_2”：

 -------------- ------------- -------------- 
| RiskName     | Mitigation  | RiskLevel    |
 -------------- ------------- -------------- 
| Risk D       | Mitigate#4  | Low          |
| Risk E       | Mitigate#5  | Low          |
| Risk F       | Mitigate#6  | Medium       |
 -------------- ------------- -------------- 

還有一個表“Risks_For_Task_3”：

 -------------- ------------- -------------- 
| RiskName     | Mitigation  | RiskLevel    |
 -------------- ------------- -------------- 
| Risk G       | Mitigate#7  | Medium       |
| Risk H       | Mitigate#8  | High         |
| Risk I       | Mitigate#9  | Medium       |
 -------------- ------------- -------------- 

還有一個“任務”表：

 -------------- ------------- 
| ID           | TaskName    |
 -------------- ------------- 
| 1            | Task#1      |
| 2            | Task#2      |
| 3            | Task#3      |
 -------------- ------------- 

我希望將 Risks_For_Task_1、Risks_For_Task_2 和 Risks_For_Task_3 組合在一起，并將它們放入一個名為“Task_Risks”的現有表中，其中一個額外的列參考了表 Tasks 中的 ID。所以結果應該是這樣的：

 -------------- ------------- -------------- -------------- 
| RiskName     | Mitigation  | RiskLevel    | TaskID       |
 -------------- ------------- -------------- -------------- 
| Risk A       | Mitigate#1  | Medium       | 1            |
| Risk B       | Mitigate#2  | Low          | 1            |
| Risk C       | Mitigate#3  | High         | 1            |
| Risk D       | Mitigate#4  | Low          | 2            |
| Risk E       | Mitigate#5  | Low          | 2            |
| Risk F       | Mitigate#6  | Medium       | 2            |
| Risk G       | Mitigate#7  | Medium       | 3            |
| Risk H       | Mitigate#8  | High         | 3            |
| Risk I       | Mitigate#9  | Medium       | 3            |
 -------------- ------------- -------------- -------------- 

這是我寫的：

INSERT INTO Task_Risks (RiskName, Mitigation, RiskLevel, TaskID)
Select RiskName, Mitigation, RiskLevel, TaskID from 
    ((Select RiskName, Mitigation, RiskLevel from Risks_For_Task_1 
        Full Join 
    Select ID from Tasks where TaskName='Task#1')
    Union All
    (Select RiskName, Mitigation, RiskLevel from Risks_For_Task_2 
        Full Join 
    Select ID from Tasks where TaskName='Task#2')
    Union All
    (Select RiskName, Mitigation, RiskLevel from Risks_For_Task_3 
        Full Join 
    Select ID from Tasks where TaskName='Task#3'));

上面的代碼給出了錯誤“無效的表名”。

uj5u.com熱心網友回復：

您不應將風險存盤在單獨的表格中；相反，您應該有一個包含所有風險的表和一個附加的任務列，該列具有對tasks表的主鍵的參考約束。

但是，這似乎是您的Task_Risks表中的內容。

使用子查詢：

INSERT INTO Task_Risks (RiskName, Mitigation, RiskLevel, TaskID)
Select RiskName,
       Mitigation,
       RiskLevel,
       (Select ID from Tasks where Task='Task#1')
from   Risks_For_Task_1 
Union All
Select RiskName,
       Mitigation,
       RiskLevel,
       (Select ID from Tasks where Task='Task#2')
from   Risks_For_Task_2 
UNION ALL
Select RiskName,
       Mitigation,
       RiskLevel,
       (Select ID from Tasks where Task='Task#3')
from   Risks_For_Task_3

或加入：

INSERT INTO Task_Risks (RiskName, Mitigation, RiskLevel, TaskID)
SELECT r.RiskName,
       r.Mitigation,
       r.RiskLevel,
       t.id
FROM   (
         Select RiskName, Mitigation, RiskLevel, 'Task#1' AS task
         from   Risks_For_Task_1 
       UNION ALL
         Select RiskName, Mitigation, RiskLevel, 'Task#2'
         from   Risks_For_Task_2 
       UNION ALL
         Select RiskName, Mitigation, RiskLevel, 'Task#3'
         from   Risks_For_Task_3
       ) r
       INNER JOIN tasks t
       ON (r.task = t.task)

uj5u.com熱心網友回復：

使用時，Union All您必須選擇要插入的所有列。因此，在這種情況下，TaskID您的 select 陳述句中缺少。

您可以將 TaskId 存盤在一個變數中并在 Select 陳述句中使用它，或者您可以使用子查詢來按照@MT0 的建議進行選擇

標籤：sql甲骨文

上一篇：where子句上的Oracle日期格式

下一篇：回傳最低分配員工