题84:
根据下面三个表为每张发票 invoice_id 编写一个SQL查询以查找以下内容:
- customer_name:与发票相关的顾客名称;
- price:发票的价格;
- contacts_cnt:该顾客的联系人数量;
- trusted_contacts_cnt:可信联系人的数量:既是该顾客的联系人又是商店顾客的联系人数量(即:可信联系人的电子邮件存在于客户表中);
- 将查询的结果按照 invoice_id 排序。
其中:
- Customers表:customer_id 是这张表的主键,此表的每一行包含了某在线商店顾客的姓名和电子邮件;
- Contacts表:user_id、 contact_email是这张表的主键,此表的每一行表示编号为 user_id 的顾客的某位联系人的姓名和电子邮件,此表包含每位顾客的联系人信息,但顾客的联系人不一定存在于顾客表中;
- Invoices:invoice_id 是这张表的主键,此表的每一行分别表示编号为 user_id 的顾客拥有有一张编号为 invoice_id、价格为 price 的发票。
解题思路:
(1)用内连接;连接发票和顾客两张表寻找发票对应的顾客信息;
(2)用左连接连接顾客和联系人两张表寻找顾客对应的连续人总数,左连接是可以统计0的情况;
(3)用左连接连接联系人和顾客两表,选择联系人是否正在顾客表中以及总数。
select i.invoice_id,
c.customer_name,
i.price,
count(c1.user_id) contacts_cnt,
count(c2.email) trusted_contacts_cnt
from Invoices i
inner join Customers c on i.user_id = c.customer_id
left join Contacts c1 on c.customer_id = c1.user_id
left join Customers c2 on c1.contact_email = c2.email
group by i.invoice_id
order by i.invoice_id;