包含連接的SQL子查詢

我正在使用 Hive hql。我正在嘗試內部連接兩個過濾表issue_type='Impediments'

現在我有一個新的要求來加入dm_jira__label包括label和issue_id列。我嘗試過使用左連接和issue_idon來添加子查詢labeldm_jira__labelissue_id

  INNER JOIN datamart_core.dm_jira__release
  ON dm_jira.issue_id =  dm_jira__release.issue_id;
    (   
        SELECT b.issue_id, b.label AS jira_label
        FROM datamart_core.dm_jira__label as B, datamart_core.dm_jira__release AS K
        LEFT JOIN b
        ON b.issue_id=k.issue_id
    );
  WHERE dm_jira.issue_type = 'Impediment') AS J

我收到以下錯誤：

AnalysisException: Illegal table reference to non-collection type: 'b' Path resolved to type: STRUCT<issue_id:DOUBLE,label:STRING>

請參閱下面的完整代碼。提前致謝。

SELECT DISTINCT
j.project_key AS jira_project_key,
j.issue_type,
j.issue_assignee AS impediment_owner,
j.issue_status AS impediment_status,
j.issue_priority AS impediment_priority,
j.issue_summary AS impediment_summary,
j.`release` AS jira_release,
j.sow AS sow_num,
j.issue_due_date_utc AS jira_issue_due_date_utc,
j.issue_id AS jira_issue_id,
s.sow_family


from (
--Subquery to combine dm_jira and dm_jira__release
  SELECT dm_jira.project_key,
  dm_jira.issue_type,
  dm_jira.issue_assignee,
  dm_jira.issue_status,
  dm_jira.issue_priority,
  dm_jira.issue_summary,
  dm_jira.issue_due_date_utc,
  dm_jira.issue_id,
  dm_jira__release.`release`,
  dm_jira__release.sow

  from datamart_core.dm_jira


  INNER JOIN datamart_core.dm_jira__release
  ON dm_jira.issue_id =  dm_jira__release.issue_id;
    (   
        SELECT b.issue_id, b.label AS jira_label
        FROM datamart_core.dm_jira__label as B, datamart_core.dm_jira__release AS K
        LEFT JOIN b
        ON b.issue_id=k.issue_id
    );
  WHERE dm_jira.issue_type = 'Impediment') AS J


INNER JOIN datamart_core.dm_asoe_jira_scrum_summary AS S
ON j.`release` = s.jira_release
AND j.sow = s.sow_num
AND j.project_key = s.jira_project_key;

uj5u.com熱心網友回復：

;結束整個陳述句，不要在子查詢或連接的末尾使用它。

使用無意義的別名，例如BorK或J會損害可讀性，不要這樣做。

FROM x, y與 相同FROM x CROSS JOIN y，它不是您要加入的表的串列。這意味著您有以下代碼...

(   
  SELECT
     b.issue_id, b.label AS jira_label
  FROM
     datamart_core.dm_jira__label as B
  CROSS JOIN
     datamart_core.dm_jira__release AS K
  LEFT JOIN
     b
       ON b.issue_id=k.issue_id
)

• b中的不是LEFT JOIN表格，會導致您的語法錯誤。

然后，您的子查詢只是位于代碼的中間，它沒有加入或以任何方式使用。我認為您打算使用更像這樣的模式...

FROM
  datamart_core.dm_jira
INNER JOIN
  datamart_core.dm_jira__release
    ON dm_jira.issue_id =  dm_jira__release.issue_id;
LEFT JOIN
(   
  <your sub-query>
)
  AS fubar
    ON fubar.something = something.else
WHERE
  dm_jira.issue_type = 'Impediment'

即使這樣，您實際上也根本不需要嵌套子查詢。您可以繼續添加連接，例如...

SELECT
  jira.project_key          AS jira_project_key,
  jira.issue_type,
  jira.issue_assignee       AS impediment_owner,
  jira.issue_status         AS impediment_status,
  jira.issue_priority       AS impediment_priority,
  jira.issue_summary        AS impediment_summary,
  jrel.`release`            AS jira_release,
  jrel.sow                  AS sow_num,
  jira.issue_due_date_utc   AS jira_issue_due_date_utc,
  jira.issue_id             AS jira_issue_id,
  jlab.label,
  summ.sow_family
FROM
  datamart_core.dm_jira                      AS jira
INNER JOIN
  datamart_core.dm_jira__release             AS jrel
    ON  jrel.issue_id = jira.issue_id
LEFT JOIN
  datamart_core.dm_jira__label               AS jlab
    ON  jlab.issue_id = jrel.issue_id
INNER JOIN
  datamart_core.dm_asoe_jira_scrum_summary   AS summ
    ON  summ.jira_release     = jrel.`release`
    AND summ.sow_num          = jrel.sow
    AND summ.jira_project_key = jira.project_key
WHERE
  jira.issue_type = 'Impediment'
;

標籤：sql 加入 蜂巢

上一篇：獲取多對多表中未映射記錄的計數

下一篇：甲骨文|與其他表比較值后更新大表