將同一列上的同一張表（多次）加入不同的表

我到處尋找，但我無法找到一種有效的方式將一個表連接到同一列上的多個表。假設，我有如下表：

員工

emp_id | emp_name | gender_id | age_range_id
----------------------------------------------
101    | adam     | 1         | 3
102    | ashley   | 2         | 2
103    | Cody     | 1         | 4

游客

visitor_id | visitor_name | gender_id | emp_id | age_range_id
---------------------------------------------------------------
501        | john         | 1         | 101    | 3
502        | lily         | 2         | 101    | null
503        | jeff         | 1         | 102    | 2

性別

gender_id | gender_name
--------------------------
1         | male
2         | female       

年齡范圍

age_range_id | age_range_name
------------------------------
1            | 18-25
2            | 26-35     
3            | 36-45
4            | 46-55
5            | 56-65

我想要的是：

emp_id | emp_name | emp_gender_and_age_range | visitor_name | visitor_gender_and_age_range
---------------------------------------------------------------------------------------------------
101    | adam     | male 36-45               | john         | male 36-45
101    | adam     | male 36-45               | lily         | female       
102    | ashley   | female 26-35             | jeff         | male 26-35       
103    | Cody     | male 46-55               | null         | null

我的代碼：

SELECT e.emp_id
      ,e.emp_name
      ,g1.gender_name || ' ' || a1.age_range_name emp_gender_and_age_range
      ,v.visitor_name
      ,g2.gender_name || ' ' || a2.age_range_name emp_gender_and_age_range
  FROM employee e
       LEFT JOIN gender g1 ON e.gender_id = g1.gender_id
       LEFT JOIN age_range a1 ON e.age_range_id = a1.age_range_id
       LEFT JOIN visitor v ON e.emp_id = v.emp_id
       LEFT JOIN gender g2 ON v.gender_id = g2.gender_id
       LEFT JOIN age_range a2 ON v.age_range_id = a2.age_range_id

有沒有一種有效的方法可以在不加入兩次的情況下在員工和訪客上顯示性別名稱？

我還在Select部分嘗試了子查詢：

SELECT e.emp_id
      ,e.emp_name
      ,(SELECT g1.gender_name || ' ' || a1.age_range_name
          FROM gender g1, age_range a1
         WHERE e.gender_id = g1.gender_id AND e.age_range_id = a1.age_range_id)
           emp_gender_and_age_range
      ,g1.gender_name || ' ' || a1.age_range_name
           emp_gender_and_age_range
      ,v.visitor_name
      ,(SELECT g2.gender_name || ' ' || a2.age_range_name
          FROM gender g2, age_range a2
         WHERE v.gender_id = g2.gender_id AND v.age_range_id = a2.age_range_id)
           visitor_gender_and_age_range
  FROM employee e
       -- LEFT JOIN gender g1 ON e.gender_id = g1.gender_id
       -- LEFT JOIN age_range a1 ON e.age_range_id = a1.age_range_id
       LEFT JOIN visitor v ON e.emp_id = v.emp_id
       -- LEFT JOIN gender g2 ON v.gender_id = g2.gender_id
       -- LEFT JOIN age_range a2 ON v.age_range_id = a2.age_range_id

但是，上面查詢的問題是，visitor_gender_and_age_range為Lily回傳null

emp_id | emp_name | emp_gender_and_age_range | visitor_name | visitor_gender_and_age_range
---------------------------------------------------------------------------------------------------
101    | adam     | male 36-45               | john         | male 36-45
101    | adam     | male 36-45               | lily         | **NULL**      
102    | ashley   | female 26-35             | jeff         | male 26-35       
103    | Cody     | male 46-55               | null         | null

注意：我沒有特別測驗上述查詢。但是，我的表格與上面的示例完全相同。

謝謝你。

uj5u.com熱心網友回復：

不。在這兩種情況下（員工和訪客），性別和年齡范圍都是查找代碼。這些代碼中的每一個都必須分別在性別和年齡范圍表中查找。

每個查找都需要自己的連接，因為每個查找都可以回傳不同的行。

uj5u.com熱心網友回復：

不，你不能，但你可以使用SQL 標量宏來使它更短/更容易閱讀，以防 Oracle 21 。

SQL Scalar 宏示例：DBFiddle 上的完整示例

create or replace function get_gender_age(p_gender_id int, p_age_range_id int)
  return varchar2 sql_macro(scalar)
is
begin
  return q'[(
    (SELECT gender_name from gender where gender_id = p_gender_id)
    ||' '||
    (SELECT age_range_name from age_range where age_range_id = p_age_range_id)
  )]';
end;
/

和查詢：

SELECT e.emp_id
      ,e.emp_name
      ,get_gender_age(e.gender_id,e.age_range_id) as emp_gender_and_age_range
      ,v.visitor_name
      ,get_gender_age(v.gender_id,v.age_range_id) as vis_gender_and_age_range
FROM employee e
     LEFT JOIN visitor v ON e.emp_id = v.emp_id

（您也可以使用行內 PL/SQL 函式，但我不建議這樣做，因為在這種情況下性能較低）

或者您可以使用 CTE：

with gender_age as (
    SELECT 
         g.gender_id
        ,a.age_range_id
        ,g.gender_name || ' ' || a1.age_range_name as gender_and_age_range
    FROM gender g, age_range a
)
SELECT e.emp_id
      ,e.emp_name
      ,g1.gender_and_age_range as emp_gender_and_age_range
      ,v.visitor_name
      ,g2.gender_and_age_range as vis_gender_and_age_range
FROM 
    employee e
    LEFT JOIN gender_age g1 USING(gender_id, age_range_id)
    LEFT JOIN visitor v ON e.emp_id = v.emp_id
    LEFT JOIN gender_age g2 USING(gender_id, age_range_id)

但無論如何，它仍然是 2 次查找。

uj5u.com熱心網友回復：

有沒有一種有效的方法可以在不加入兩次的情況下在員工和訪客上顯示性別名稱？

加入兩次是根據關系代數加入這些關系的標準方法。

此外，您還需要考慮，如果索引gender (gender_id)可用，則加入兩次可能會非常有效。我不會花時間來解決這兩個連接。

我看不出你對此有什么擔憂。

標籤：sql 甲骨文

上一篇：如何將嵌套陣串列示為一維陣列？

下一篇：First_Day/Last_Day查詢