代码之家 › 专栏 › 技术社区 › HUNG

将左连接分隔成若干列

sql mysql

HUNG · 技术社区 · 6 年前

我有一些表是左连接的。但是,有一个表叫做Form the,我不知道如何将该表与table User结合起来。

p、由于某些原因,表单记录行被保留并插入一个新行,而不是在取消操作后更新表单状态。

表用户

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;

1 回复 | 直到 6 年前

Yogesh Sharma 6 年前

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

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;

推荐文章

Badal Solanki · 如何在MySQL中对字符串进行算术运算?

1 年前

Saleh Mehdiyev · 我想重构我的Laravel代码以遵守SOLID原则

1 年前

hello_programmers · Mysql从其他表输出一列

1 年前

Community wiki · 这个MySQL语句出了什么问题?

1 年前

Community wiki · 优化从同一表中提取的多列的查询

1 年前