从一个业务需求充分学习连接查询

# 需求内容

查询到所有邮箱用户的组织id、邮箱地址、姓名、别名账号

# 背景

① 邮箱账号和别名账号(primary_email)不在同一个表；

② 邮箱账号分为 user_id 和 @ 和 域名名称(domain_name) 举个例子 carryup@mail.cn 那么carryup就是user_id，mail.cn 就是域名名称，完整的账号我们称之为"userAtDomain"；

③ 因为域名名称是在管理后台是可以更改的，因此，表内是不会存储userAtDomain的，只会存储user_id和组织id(org_id)和域名名称domain_name；

 # 所有的表

cm_user_info
org_id	user_id	true_name
组织id	用户id	姓名

td_user
org_id	user_id	domain_id
组织id	用户id	域名编号

td_domain
domain_id	domain_name
域名编号	域名名称

td_smtp_alias
alias_user_id	org_id	user_id
别名id	组织id	用户id

cm_user_info 应为主表，因为它包含的数据是最有用的，org_id+user_id是唯一标识，在同一个组织下user_id是唯一的，即使domain_name不一样，user_id也不能相同。

td_user，因为cm_user_info里面没有域名信息，所以需要td_user，但是td_user只有domain_id，没有domain_name（很离谱，多加个字段会死吗？）

td_domain，只是用来找到domain_name

td_smtp_alias，别名表，也算是很重要的表，通过org_id+user_id与cm_user_info链接即可得到别名信息

# 分解步骤分析

① 先找到所有用户的组织id、user_id、姓名

select u.org_id,u.user_id,u.true_name from cm_user_info;

 ② 找到所有用户的邮箱地址（userAtDomain）

因为td_user只有domain_id，所以还需要再链接 td_domain 找到 domain_name

select td_user.org_id,td_user.user_id,td_user.user_id, td_domain.domain_name,
       td_user.domain_id,info.true_name
from td_user
         left join cm_user_info as info on (td_user.user_id = info.user_id and td_user.org_id = info.org_id)
         left join td_domain on (td_domain.domain_id = td_user.domain_id);

因为我们需要直接将user_id和domain_name拼接起来，所以需要调用sql的拼接函数concat

select td_user.org_id,td_user.user_id,concat(td_user.user_id, '@', td_domain.domain_name) as email,info.true_name
from td_user
         left join cm_user_info as info on (td_user.user_id = info.user_id and td_user.org_id = info.org_id)
         left join td_domain on (td_domain.domain_id = td_user.domain_id);

 

③ 将查询到userAtDomain的查询当成一个子查询，我们就能得到除了别名以外的所有信息了

select u.org_id,user_name_table.email,u.true_name
        from cm_user_info as u
         left join (select td_user.org_id,td_user.user_id,concat(td_user.user_id, '@', td_domain.domain_name) as email,info.true_name
                    from td_user
                             left join cm_user_info as info on (td_user.user_id = info.user_id and td_user.org_id = info.org_id)
                             left join td_domain on (td_domain.domain_id = td_user.domain_id)) as user_name_table
                   on (u.user_id = user_name_table.user_id and u.org_id = user_name_table.org_id)
         left join td_smtp_alias as al on (u.user_id = al.user_id and u.org_id = al.org_id);

 

 

 ④ 找到别名

因为org_id+user_id是唯一的，所以我们可以通过这个连接td_smtp_domain和cm_user_info，这样就可以得到别名的user_id

select u.user_id,u.org_id,al.alias_user_id,al.domain_id from cm_user_info as u
        left join  td_smtp_alias as al on (u.user_id = al.user_id and u.org_id = al.org_id);

 

⑤ 拼接出完整的别名地址

select u.user_id,u.org_id,concat(al.alias_user_id,'@',d.domain_name) as alias from cm_user_info as u
        left join  td_smtp_alias as al on (u.user_id = al.user_id and u.org_id = al.org_id)
        left join td_domain d on (d.domain_id = al.domain_id);

 

⑥ 整合

因为已经有了完整的别名地址（步骤五）还有完整的用户主邮件地址（步骤三），此时我们将所有查询都整理到一起就可以得到我们想要的结果

select u.org_id,user_name_table.email,u.true_name,group_concat(concat(al.alias_user_id,'@',d.domain_name)) as alias
         from cm_user_info as u
         left join (select td_user.org_id,td_user.user_id,concat(td_user.user_id, '@', td_domain.domain_name) as email,info.true_name
                    from td_user
                             left join cm_user_info as info on (td_user.user_id = info.user_id and td_user.org_id = info.org_id)
                             left join td_domain on (td_domain.domain_id = td_user.domain_id)) as user_name_table
                   on (u.user_id = user_name_table.user_id and u.org_id = user_name_table.org_id)
         left join td_smtp_alias as al on (u.user_id = al.user_id and u.org_id = al.org_id)
         left join td_domain d on (d.domain_id = al.domain_id)
group by email;

 

 

 

因为每个账号的别名不是只有一个，如果我们要一起显示不分行，我们可以加“group by email”

 

# 结论

由此我们可以很容易地得出结论，不管是再怎么复杂的查询，我们只需要将需求拆分，把所有需要的信息查询出来后连接起来就可以了。然后结合一起简单的函数，比如group by、group_concat、concat，就可以应付几乎所有的查询