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

最新推荐文章于 2021-02-09 21:08:01 发布

原创最新推荐文章于 2021-02-09 21:08:01 发布 · 644 阅读

0 ·

CC 4.0 BY-SA版权

Learning Day 同时被 3 个专栏收录

10 篇文章

订阅专栏

201812

3 篇文章

订阅专栏

MySQL

2 篇文章

订阅专栏

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

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