mysql - 基于列合并表

Question

我正在尝试根据 roles.ref_type 列将三个表合并为一个表。 roles.ref_type 列具有值 A(代表 employee 表)和 B (代表 组织 表)。

roles

role_id     role_type    company     ref_id
   1            A           X           1
   2            B           Y           4
   3            A           Z           2
   4            B           X           5

employee (A)

employee_id    employee_name     joining_date
     1           Einstein         24/01/1998
     2           Maxwell          16/03/2002
     3           Graham           23/05/2006
     4           Boltz            06/02/2008

organisations (B)

org_id        org_name         org_max     org_location
  1           Stackoverflow      300           NY
  2           StackExchange       45           OR
  3           Facebook           300           NY
  4           Google              45           OR
  5           Yahoo              300           NY
  6           Rediff              45           OR

预期结果:

role_id      role_type      company     ref_id    ref_name       ref_joining_date     ref_org_max
   1             A             X           1      Einstein          24/01/1998             NULL
   2             B             Y           4      Stackexchange        NULL                45
   3             A             Z           2      Maxwell           16/03/2002             NULL
   4             B             X           5      Yahoo                NULL                300

我尝试了以下查询:

SELECT t1.role_id, t1.role_type, t1.company, t1.ref_id,
    CASE t1.role_type
        WHEN "A"
        THEN (
            SELECT 
                t2.employee_name as ref_name, 
                t2.joining_date as ref_joining_date,
                NULL as ref_org_max
            FROM
                employee t2
        )
        WHEN "B"
        THEN (
            SELECT 
                t3.org_name as ref_name, 
                NULL as ref_joining_date,
                t3.org_max as ref_org_max
            FROM
                organisations t3
        )
    END 
FROM roles t1

这不起作用，因为我知道上述查询无效，但我正在寻找类似的查询来获得结果。

Answer 1

您需要使用LEFT OUTER JOIN 而不是CASE 语句

试试这个

SELECT t1.role_id,
       t1.role_type,
       t1.company,
       t1.ref_id,
       COALESCE(e.employee_name, o.org_id) AS ref_name,
       e.joining_date                      AS ref_joining_date,
       o.org_max                           AS ref_org_max
FROM   roles t1
       LEFT JOIN employee
              ON e.employee_id = t1.ref_id
                 AND t1.role_type = 'A'
       LEFT JOIN organisations o
              ON o.org_id = t1.ref_id
                 AND t1.role_type = 'B'