不幸的是，我不确定我要编写的查询是否有特定名称。我的问题是以下问题，我创建了两个临时表，一个临时表包含通过IVR或通过电子邮件“退出”通信的客户列表。
mysql> desc tt_customers;
+------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | MUL | 0 | |
| name | varchar(40) | NO | | NULL | |
+------------------+------------------+------+-----+---------+-------+
mysql> desc tt_opt_outs;
+-----------------------+----------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+----------------------------------------+------+-----+---------+-------+
| customer_id | int(10) unsigned | NO | MUL | NULL | |
| event_type | enum('PRE_PEAK_TIME','POST_PEAK_TIME' | YES | | NULL | |
| notification_channel | enum('EMAIL','IVR') | NO | | NULL | |
+-----------------------+----------------------------------------+------+-----+---------+-------+
并非客户表中的所有客户都将在“退出”表中。选择退出表中的客户可以在那里选择退出，包括EMAIL，IVR或同时选择两者，以及任何事件类型。我想创建一个具有以下列标题
customer_id，name，IVR Optout，Email Optout的报告，其中IVR和“电子邮件退出”列与退出event_type无关。我不确定如何构造联接/子查询/联合，或者在这里需要什么来创建所需的确切查询。任何帮助，将不胜感激！ 最佳答案
除了case语句，您还可以使用左外部联接。
查询（左外部联接）
select c.id as customer_id , c.name,ti.notification_channel as IVR,
te.notification_channel as EMAIL from tt_customers c
left outer join tt_opt_outs ti on c.id = ti.customer_id and ti.notification_channel = 'IVR'
left outer join tt_opt_outs te on c.id = te.customer_id and te.notification_channel = 'EMAIL'
输出：
数据设置：
create table tt_customers (id int(10), name varchar(40));
create table tt_opt_outs (customer_id int(10), event_type enum('PRE_PEAK_TIME','POST_PEAK_TIME'), notification_channel enum('EMAIL','IVR') );
insert into tt_customers values (1,"all in");
insert into tt_customers values(2,"email out");
insert into tt_customers values(3,"ivr out");
insert into tt_customers values(4,"all out");
insert into tt_opt_outs values(2,'PRE_PEAK_TIME','EMAIL');
insert into tt_opt_outs values(3,'PRE_PEAK_TIME','IVR');
insert into tt_opt_outs values(4,'PRE_PEAK_TIME','EMAIL');
insert into tt_opt_outs values(4,'PRE_PEAK_TIME','IVR');
SQL小提琴：http://sqlfiddle.com/#!9/0e82a7/17
关于mysql - 令人困惑的MySqL JOIN，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50342192/