文章目录
力扣高频SQL 50题(基础版)第八题
1581. 进店却未进行过交易的顾客
题目说明
表:Visits
±------------±--------+
| Column Name | Type |
±------------±--------+
| visit_id | int |
| customer_id | int |
±------------±--------+
visit_id 是该表中具有唯一值的列。
该表包含有关光临过购物中心的顾客的信息。
表:Transactions
±---------------±--------+
| Column Name | Type |
±---------------±--------+
| transaction_id | int |
| visit_id | int |
| amount | int |
±---------------±--------+
transaction_id 是该表中具有唯一值的列。
此表包含 visit_id 期间进行的交易的信息。
有一些顾客可能光顾了购物中心但没有进行交易。请你编写一个解决方案,来查找这些顾客的 ID ,以及他们只光顾不交易的次数。
返回以 任何顺序 排序的结果表。
思路分析
本题利用子查询
的方法结合逻辑查询not
解题,首先在表Transactions中查出光顾且消费的顾客ID,然后在表Visits中过滤掉消费的顾客ID,即可得到进店却为进行交易的顾客,最后通过count
函数统计次数。
实现过程
准备数据:
Create table If Not Exists Visits(visit_id int, customer_id int)
Create table If Not Exists Transactions(transaction_id int, visit_id int, amount int)
Truncate table Visits
insert into Visits (visit_id, customer_id) values ('1', '23')
insert into Visits (visit_id, customer_id) values ('2', '9')
insert into Visits (visit_id, customer_id) values ('4', '30')
insert into Visits (visit_id, customer_id) values ('5', '54')
insert into Visits (visit_id, customer_id) values ('6', '96')
insert into Visits (visit_id, customer_id) values ('7', '54')
insert into Visits (visit_id, customer_id) values ('8', '54')
Truncate table Transactions
insert into Transactions (transaction_id, visit_id, amount) values ('2', '5', '310')
insert into Transactions (transaction_id, visit_id, amount) values ('3', '5', '300')
insert into Transactions (transaction_id, visit_id, amount) values ('9', '5', '200')
insert into Transactions (transaction_id, visit_id, amount) values ('12', '1', '910')
insert into Transactions (transaction_id, visit_id, amount) values ('13', '2', '970')
实现方式:
select customer_id,count(customer_id) count_no_trans from Visits where visit_id not in (select visit_id from Transactions) group by customer_id;
结果截图:
总结:
#逻辑查询not
#查询不是河南的学生
select * from stu where address not in ('河南')
标签:customer,insert,into,visit,50,力扣,values,SQL,id
From: https://blog.csdn.net/m0_70882914/article/details/140638790