1364 顾客的可信联系人数量 完成表前半部分，在完成表的后半部分

Create table If Not Exists Customers (customer_id int, customer_name varchar(20), email varchar(30))
Create table If Not Exists Contacts (user_id int, contact_name varchar(20), contact_email varchar(30))
Create table If Not Exists Invoices (invoice_id int, price int, user_id int)
Truncate table Customers
insert into Customers (customer_id, customer_name, email) values ('1', 'Alice', 'alice@leetcode.com')
insert into Customers (customer_id, customer_name, email) values ('2', 'Bob', 'bob@leetcode.com')
insert into Customers (customer_id, customer_name, email) values ('13', 'John', 'john@leetcode.com')
insert into Customers (customer_id, customer_name, email) values ('6', 'Alex', 'alex@leetcode.com')
Truncate table Contacts
insert into Contacts (user_id, contact_name, contact_email) values ('1', 'Bob', 'bob@leetcode.com')
insert into Contacts (user_id, contact_name, contact_email) values ('1', 'John', 'john@leetcode.com')
insert into Contacts (user_id, contact_name, contact_email) values ('1', 'Jal', 'jal@leetcode.com')
insert into Contacts (user_id, contact_name, contact_email) values ('2', 'Omar', 'omar@leetcode.com')
insert into Contacts (user_id, contact_name, contact_email) values ('2', 'Meir', 'meir@leetcode.com')
insert into Contacts (user_id, contact_name, contact_email) values ('6', 'Alice', 'alice@leetcode.com')
Truncate table Invoices
insert into Invoices (invoice_id, price, user_id) values ('77', '100', '1')
insert into Invoices (invoice_id, price, user_id) values ('88', '200', '1')
insert into Invoices (invoice_id, price, user_id) values ('99', '300', '2')
insert into Invoices (invoice_id, price, user_id) values ('66', '400', '2')
insert into Invoices (invoice_id, price, user_id) values ('55', '500', '13')
insert into Invoices (invoice_id, price, user_id) values ('44', '60', '6')

SELECT f.invoice_id,f.customer_name,f.price,e.contacts_cnt,e.trusted_contacts_cnt

FROM
    #获取到每个发票的id，顾客名称，价格
    (select  invoice_id,b.customer_name,price
    from invoices a 
    left join Customers b on a.user_id = b.customer_id ) f
left join 
(   #计算顾客有几位；联系人 ， 在顾客表里面 利用sum（if ）计算联系表在顾客表的人数
    SELECT c.customer_name,count(d.contact_name) as 'contacts_cnt' , sum(if(d.contact_name in 
	(select customer_name
	from Customers),1,0  )    ) as 'trusted_contacts_cnt'
    from Customers c 
    left join Contacts d  on c.customer_id = d.user_id
    group by c.customer_name 
#上述完成了表的后半部分
#根据姓名做连接
) e on f.customer_name = e.customer_name

order by f.invoice_id 

select *

from invoices a 
left join Customers b on a.user_id = b.customer_id

SELECT customers_name,count(d.contact_name) as 'contacts_cn' , sum(if(d.contact_name in 
	(select customer_name
	from Customers),1,0  )    ) as 'trusted_contacts_cnt '

from Customers c 

left join Contacts d  on c.customer_id = d.user_id

group by customers_name

SELECT c.customer_name,count(d.contact_name) as 'contacts_cn' , sum(if(d.contact_name in 
	(select customer_name
	from Customers),1,0  )    ) as 'trusted_contacts_cnt '

from Customers c 

left join Contacts d  on c.customer_id = d.user_id

group by c.customer_name