【MySQL】ANY函数 的巧用(筛选字段 = ANY(语句))

2024-01-09 16:47:10

力扣题

1、题目地址

1364. 顾客的可信联系人数量

2、模拟表

顾客表:Customers

Column NameType
customer_idint
customer_namevarchar
emailvarchar
  • customer_id 是这张表具有唯一值的列。
  • 此表的每一行包含了某在线商店顾客的姓名和电子邮件。

联系方式表:Contacts

Column NameType
user_idid
contact_namevarchar
contact_emailvarchar
  • (user_id, contact_email) 是这张表的主键(具有唯一值的列的组合)。
  • 此表的每一行表示编号为 user_id 的顾客的某位联系人的姓名和电子邮件。
  • 此表包含每位顾客的联系人信息,但顾客的联系人不一定存在于顾客表中。

发票表:Invoices

Column NameType
invoice_idint
priceint
user_idint
  • invoice_id 是这张表具有唯一值的列。
  • 此表的每一行分别表示编号为 user_id 的顾客拥有有一张编号为 invoice_id、价格为 price 的发票。

3、要求

为每张发票 invoice_id 编写一个查询方案以查找以下内容:

  • customer_name:与发票相关的顾客名称。
  • price:发票的价格。
  • contacts_cnt:该顾客的联系人数量
  • trusted_contacts_cnt:可信联系人的数量:既是该顾客的联系人又是商店顾客的联系人数量
    (即:可信联系人的电子邮件存在于 Customers 表中)。
  • 返回结果按照 invoice_id 排序。

结果的格式如下例所示。

示例 1:

输入:
Customers 表:

customer_idcustomer_nameemail
1Alicealice@leetcode.com
2Bobbob@leetcode.com
13Johnjohn@leetcode.com
6Alexalex@leetcode.com

Contacts 表:

user_idcontact_namecontact_email
1Bobbob@leetcode.com
1Johnjohn@leetcode.com
1Jaljal@leetcode.com
2Omaromar@leetcode.com
2Meirmeir@leetcode.com
6Alicealice@leetcode.com

Invoices 表:

invoice_idpriceuser_id
771001
882001
993002
664002
5550013
44606

输出:

invoice_idcustomer_namepricecontacts_cnttrusted_contacts_cnt
44Alex6011
55John50000
66Bob40020
77Alice10032
88Alice20032
99Bob30020

解释:
Alice 有三位联系人,其中两位(Bob 和 John)是可信联系人。
Bob 有两位联系人, 他们中的任何一位都不是可信联系人。
Alex 只有一位联系人(Alice),并是一位可信联系人。
John 没有任何联系人。

4、代码编写

代码

SELECT one.invoice_id, 
       two.customer_name, 
       one.price, 
       COUNT(three.user_id) AS contacts_cnt, 
       IFNULL(SUM(three.contact_email = ANY(SELECT email FROM Customers)), 0) AS trusted_contacts_cnt
FROM Invoices one
    LEFT JOIN Customers two ON one.user_id = two.customer_id
    LEFT JOIN Contacts three on two.customer_id = three.user_id
GROUP BY one.invoice_id
ORDER BY one.invoice_id

代码分析

1、根据发票找到对应顾客名称

SELECT two.customer_name
FROM Invoices one
	LEFT JOIN Customers two ON one.user_id = two.customer_id

2、从输出结果看出得根据发票 invoice_id 分组且排序根据发票 invoice_id 顺序,取得 发票价格

SELECT one.invoice_id, 
	   one.price
FROM Invoices one
GROUP BY one.invoice_id
GROUP BY one.invoice_id

3、顾客的联系人数量(Customers 顾客表对应 Contacts 联系人表)(two.customer_id = three.user_id)

SELECT COUNT(three.user_id) AS contacts_cnt
FROM Invoices one
    LEFT JOIN Customers two ON one.user_id = two.customer_id
    LEFT JOIN Contacts three on two.customer_id = three.user_id
GROUP BY one.invoice_id
ORDER BY one.invoice_id

4、可信联系人的数量是顾客联系人又是商店顾客的联系人数量,那就说明 Contacts 的对应联系人的邮箱存在于 Customers 表中

SELECT IFNULL(SUM(three.contact_email = ANY(SELECT email FROM Customers)), 0) AS trusted_contacts_cnt
FROM Invoices one
    LEFT JOIN Customers two ON one.user_id = two.customer_id
    LEFT JOIN Contacts three on two.customer_id = three.user_id
GROUP BY one.invoice_id
ORDER BY one.invoice_id

知识点

语法:筛选字段 = ANY(语句)
作用:只要 筛选字段 满足等于 语句 中的某一个值就返回True

语法:SUM(条件表达式)
作用:条件表达式如果为True,则加1,默认0(类似Count函数)

参考

SQL Any运算符

文章来源:https://blog.csdn.net/weixin_50223520/article/details/135482173
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。