力扣---高频SQL题---8---1581. 进店却未进行过交易的顾客

最新推荐文章于 2024-08-21 10:11:50 发布

@白圭

最新推荐文章于 2024-08-21 10:11:50 发布

阅读量252

点赞数 2

文章标签： leetcode sql 算法

本文链接：https://blog.csdn.net/taiyuezyh/article/details/136217901

版权

文章介绍了如何通过SQL查询在MySQL中连接两个表Visits和Transactions，找出那些在Transactions中交易_id缺失的客户，并计算每个客户的访问次数。展示了左连接操作和计数的方法，最后给出了运行结果。

摘要由CSDN通过智能技术生成

一、题目

在这里插入图片描述

二、代码

# Write your MySQL query statement below  先连接表  然后再看

-- select Visits.customer_id,count(Visits.customer_id ) from Visits
-- group by Visits.customer_id


-- select distinct Transactions.visit_id from Transactions

-- select Visits.customer_id,  count(Transactions.transaction_id ) from Visits
-- left join Transactions
-- on Visits.visit_id  = Transactions.visit_id 
-- where transaction_id is null
-- group by customer_id 

select table1.customer_id ,count(table1.customer_id ) as count_no_trans  from
(
select Visits.customer_id ,  Transactions.transaction_id  from Visits
left join Transactions
on Visits.visit_id  = Transactions.visit_id 
where transaction_id is null 
) as table1
group by table1.customer_id