混乱的mysql连接

hc8w905p 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(282)

不幸的是，我不确定是否有一个特定的名称为我试图写的查询。我的问题是，我创建了两个临时表，其中一个表列出了在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    |       |
+-----------------------+----------------------------------------+------+-----+---------+-------+

不是customer表中的所有客户都将在opt-out表中。选择退出表中的客户可以选择退出电子邮件、ivr或两者，以及任何事件类型。我想用以下列标题创建一个报表 customer_id , name , IVR Optout , Email Optout ，其中ivr和email opt-out列与opt-out无关 event_type . 我不知道如何构造一个连接/子查询/联合或任何我需要在这里创建确切查询的东西。任何帮助都将不胜感激！

mysql Join left-join

来源：https://stackoverflow.com/questions/50342192/confusing-mysql-join

2条答案

按热度按时间

4urapxun1#

除了case语句外，还可以使用left-outer连接。
查询（左外联接）

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/#!2017年9月8日

赞(0）回复(0）举报 2021-06-21

bwleehnv2#

下面是将为您提供所需结果的sql：

create table tt_customers(id int,name varchar(40));
create table tt_opt_outs(customer_id int,event_type enum('PRE_PEAK_TIME','POST_PEAK_TIME'),notification_channel enum('EMAIL','IVR'));

insert into tt_customers values(1,'ABC');
insert into tt_customers values(2,'XYZ');
insert into tt_opt_outs values(1,'PRE_PEAK_TIME','EMAIL');
insert into tt_opt_outs values(2,'POST_PEAK_TIME','IVR');

您对所需结果的查询：

select c.id as customer_id,
    c.name,
    case when t.notification_channel = 'IVR' then 'Yes' else null end ivr_optout,
    case when t.notification_channel = 'EMAIL' then 'Yes' else null end email_optout
from tt_customers c
   left join tt_opt_outs t
   on (c.id = t.customer_id);

赞(0）回复(0）举报 2021-06-21

我来回答

混乱的mysql连接

2条答案

您对所需结果的查询：

相关问题

热门标签

最新问答