mysql - 将 LEFT JOIN 分成一些列

Question

我有一些未连接的表。但是，有一个名为 Form 的表，我不知道如何将该表与 Table User 结合起来。

假设有 3 个表单，分别名为 form1、form2 和 form3。我想获取每个电子邮件用户的每个表单的最新状态。

附:由于某些原因，表单记录行被保留并插入新行，而不是在取消操作后仅更新表单状态。

我必须使用 union all 吗？

表用户

id    |  email
----------------------------
1     | testing1@testing.com
2     | testing2@testing.com
3     | testing3@testing.com

表格

email                    |  form  |  form_status | date
----------------------------------------------------------------------
testing1@testing.com     |  form1 |  completed   | 2018-08-01 12:00:00
testing1@testing.com     |  form2 |  cancelled   | 2018-08-02 12:00:00
testing1@testing.com     |  form2 |  completed   | 2018-08-03 12:00:00
testing1@testing.com     |  form3 |  cancelled   | 2018-08-04 12:00:00
testing2@testing.com     |  form1 |  cancelled   | 2018-08-05 12:00:00
testing2@testing.com     |  form2 |  completed   | 2018-08-06 12:00:00

我的目标是获得以下结果

id    |  email               | form1     | form2     | form3
-----------------------------------------------------------------
1     | testing1@testing.com | completed | completed | cancelled
2     | testing2@testing.com | cancelled | completed | null
3     | testing3@testing.com | null      | null      | null 

我尝试了类似的方法，但仍然出现错误。

SELECT u.id, q.form_status as form1, u.email, 
u.last_successful_login as 'last login in', 
x.c1 as 'xc1', z.c2 as 'zc2' 
FROM user u 
left join yyyy x on u.email = x.email 
left join zzzz z on u.email = z.email 
UNION (
    SELECT
      form_status
    FROM Form f
    WHERE f.email = u.email and f.formtype = 'form1'
  ) q
ORDER BY u.id;

Answer 1

您需要使用子查询进行条件聚合:

select u.id, u.email, 
       max(case when f.form = 'form1' then f.form_status end) form1,
       max(case when f.form = 'form2' then f.form_status end) form2,
       max(case when f.form = 'form3' then f.form_status end) form3
from users u left join
     form f
     on f.email = u.email and 
        f.date = (select max(f1.date)
                  from form f1
                  where f1.email = f.email and f1.form = f.form
                 )
group by u.id, u.email;