我有一些桌子留着加入。但是，有一个名为Form的表，我不确定如何将该表与Table User合并。
假设存在三种名为form1，form2和form3的形式。我想为每个电子邮件用户获取每个表单的最新状态。
ps。由于某些原因，保留表单记录行并插入新行，而不仅仅是在执行取消操作后更新表单状态。
我必须全部使用工会吗？
表用户
id | email
----------------------------
1 | [email protected]
2 | [email protected]
3 | [email protected]
表格形式
email | form | form_status | date
----------------------------------------------------------------------
[email protected] | form1 | completed | 2018-08-01 12:00:00
[email protected] | form2 | cancelled | 2018-08-02 12:00:00
[email protected] | form2 | completed | 2018-08-03 12:00:00
[email protected] | form3 | cancelled | 2018-08-04 12:00:00
[email protected] | form1 | cancelled | 2018-08-05 12:00:00
[email protected] | form2 | completed | 2018-08-06 12:00:00
我的目标是取得以下结果
id | email | form1 | form2 | form3
-----------------------------------------------------------------
1 | [email protected] | completed | completed | cancelled
2 | [email protected] | cancelled | completed | null
3 | [email protected] | 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;
最佳答案
您需要使用子查询进行条件聚合：
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;
关于mysql - 将LEFT JOIN分成几列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/51944945/