如何在JOIN中的JOIN中引用字段？

Question

在JOIN语句中的子查询中选择特定字段时，使用点符号来引用table.field_name，但是，在第一个JOIN中使用另一个子查询JOIN时要执行什么操作？

我的JOIN瓦特/例如JOIN：

JOIN (SELECT 
BUDGET.protocol_id, BUDGET.completed_date, 
CONTRACT.completed_date, 
REQUEST.completed_date, 
RECEIVE.completed_date, 
PC.completed_date, 
FC.completed_date, 
MGR.completed_date 

FROM (SELECT completed_date, task_list_id, protocol_id FROM task WHERE task_name LIKE 'Budget%') BUDGET 

JOIN (SELECT completed_date, task_list_id, protocol_id FROM task WHERE task_name LIKE 'Contract%') CONTRACT 
    ON BUDGET.protocol_id = CONTRACT.protocol_id 
    AND BUDGET.task_list_id = CONTRACT.task_list_id 

JOIN (SELECT completed_date, task_list_id, protocol_id FROM task WHERE task_name LIKE 'Request%') REQUEST 
    ON BUDGET.protocol_id = REQUEST.protocol_id 
    AND BUDGET.task_list_id = REQUEST.task_list_id 

JOIN (SELECT completed_date, task_list_id, protocol_id FROM task WHERE task_name LIKE 'Protocol%') PC 
    ON BUDGET.protocol_id = PC.protocol_id 
    AND BUDGET.task_list_id = PC.task_list_id 

JOIN (SELECT completed_date, task_list_id, protocol_id FROM task WHERE task_name LIKE 'Financials%') FC 
    ON BUDGET.protocol_id = FC.protocol_id 
    AND BUDGET.task_list_id = FC.task_list_id 

JOIN (SELECT completed_date, task_list_id, protocol_id FROM task WHERE task_name LIKE 'Manager%') MGR 
    ON BUDGET.protocol_id = MGR.protocol_id 
    AND BUDGET.task_list_id = MGR.task_list_id 

JOIN (SELECT completed_date, task_list_id, protocol_id FROM task WHERE task_name LIKE 'Receive%') RECEIVE 
    ON BUDGET.protocol_id = RECEIVE.protocol_id 
    AND BUDGET.task_list_id = RECEIVE.task_list_id 
) TASK ON PCL.protocol_id = TASK.BUDGET.protocol_id 

我一直在尝试与子查询要做的就是选择特定的任务完成日期，所以在SELECT语句我的直觉是使用TASK.BUDGET.completed_date，用于每个任务。然而，我得到一个ORA-00918：含糊不清的列，因此似乎有什么问题都用completed_date

Answer 1

使用条件汇总：

SELECT t.protocol_id, 
     MAX(CASE WHEN task_name LIKE 'Budget%' THEN completed_date END) as budget_completed_date, 
     . . . 
FROM task t 
GROUP BY protocol_id; 

我不太清楚如何task_list_id结合使用。您可能也希望通过这种方式进行汇总。

Answer 2

如果需要访问的子查询子查询的栏目，然后简单地在中间的子查询SELECT使用它们：中

SELECT middle_subquery.c1 
FROM 
(
    SELECT inner_subquery.c1 
    FROM 
    (
     SELECT count(*) c1 FROM tab 
    ) inner_subquery 
) middle_subquery 

代替

SELECT middle_subquery.inner_subquery.c1 
FROM 
(
    SELECT * 
    FROM 
    (
     SELECT count(*) c1 FROM tab 
    ) inner_subquery 
) middle_subquery 

Answer 3

采取了一些了解，但我认为我得到了雅。

SELECT 
BUDGET.protocol_id, 
BUDGET.completed_date, 
CONTRACT.completed_date, 
REQUEST.completed_date, 
RECEIVE.completed_date, 
PC.completed_date, 
FC.completed_date, 
MGR.completed_date 
... 
)task 

因此，'budget.completed_date'现在可以称为'task.completed_date'。所以pc.completed_date等也可以mgr.completed_date等这是它的困惑。只是别名：

SELECT 
BUDGET.protocol_id as budget_protocol_id 
BUDGET.completed_date as budget_completed_date, 
    SELECT 
BUDGET.protocol_id, BUDGET.completed_date, 
CONTRACT.completed_date as contract_completed_date, 
REQUEST.completed_date as request_completed_date, 
RECEIVE.completed_date as receive_completed_date, 
etc 
... 
)task 

这将让每个task.complete_date独特的，你可以参考他们从那里进行task.budget_completed_date。合理？