MySQL求每个用户当月是否消费过

儒冠多误身

于 2018-12-01 16:26:34 发布

阅读量626

点赞数

分类专栏： Learning Day 201812 MySQL

本文链接：https://blog.youkuaiyun.com/DataFrame/article/details/84674889

版权

Learning Day 同时被 3 个专栏收录

10 篇文章

订阅专栏

201812

3 篇文章

订阅专栏

MySQL

2 篇文章

订阅专栏

本文详细介绍了如何使用SQL进行复杂的数据查询，包括通过LEFT JOIN实现数据对比，以及如何利用视图提高查询效率。文章提供了具体的SQL代码示例，展示了如何从订单信息中筛选已支付记录，并按用户ID和月份分组，最后通过日期比较实现数据连续性检查。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT userId,DATE_FORMAT(paidTime,"%Y%m-01")
from learn.order_info_utf
GROUP BY userId,DATE_FORMAT(paidTime,"%Y%m-01")

使用left join

SELECT t1.m,count(t1.m),count(t2.m) FROM (
	SELECT userId,DATE_FORMAT(paidTime,"%Y-%m-01") as m
	from learn.order_info_utf
	WHERE Ispaid = "已支付"
	GROUP BY userId,DATE_FORMAT(paidTime,"%Y-%m-01")) t1
left JOIN (
	SELECT userId,DATE_FORMAT(paidTime,"%Y-%m-01") as m
	from learn.order_info_utf
	WHERE Ispaid = "已支付"
	GROUP BY userId,DATE_FORMAT(paidTime,"%Y-%m-01")) t2
ON  t1.userId = t2.userId and t1.m = DATE_SUB(t2.m,INTERVAL -1 MONTH)
GROUP BY t1.m

*注意【%Y-%m-01】之间的【-】

最好创建view，更有效率

create algorithm = undefined view tmp
as  
SELECT userId,DATE_FORMAT(paidTime,"%Y%m-01")
from learn.order_info_utf
GROUP BY userId,DATE_FORMAT(paidTime,"%Y%m-01")

SELECT t1.m,count(t1.m),count(t2.m) FROM tmp as t1
left JOIN tmp as  t2
ON  t1.userId = t2.userId and t1.m = DATE_SUB(t2.m,INTERVAL -1 MONTH)
GROUP BY t1.m