开发邀请有礼功能遇到需要查询连续月份@[toc]
需求:1级客户邀请2级客户后,二级客户每个月付款后给1级客户返现,要求在邀请时间节点以后2级必须保持每个月都有付款记录,不可以中断
表结构
CREATE TABLE `test_month` (
`pk_id` int(11) NOT NULL COMMENT 'id',
`order_month` date DEFAULT NULL COMMENT '月份'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
INSERT INTO `lepao_crm_test`.`test_month` (`pk_id`, `order_month`) VALUES (1, '2023-02');
INSERT INTO `lepao_crm_test`.`test_month` (`pk_id`, `order_month`) VALUES (1, '2023-03');
INSERT INTO `lepao_crm_test`.`test_month` (`pk_id`, `order_month`) VALUES (1, '2023-04');
INSERT INTO `lepao_crm_test`.`test_month` (`pk_id`, `order_month`) VALUES (1, '2023-05');
INSERT INTO `lepao_crm_test`.`test_month` (`pk_id`, `order_month`) VALUES (1, '2023-06');
INSERT INTO `lepao_crm_test`.`test_month` (`pk_id`, `order_month`) VALUES (1, '2023-07');
INSERT INTO `lepao_crm_test`.`test_month` (`pk_id`, `order_month`) VALUES (1, '2023-08');
思路分析
起一个临时变量做为排序值 通过日期月份相减得出一个同值日期 进行分组就能得出连续的次数数据
解析1:
SET @row_number = 1;
SELECT
( @row_number := @row_number + 1 ) AS ROW_NUMBER,
pk_id,
order_month
FROM
test_month
WHERE
pk_id = '1'
ORDER BY
pk_id,
order_month
可以看出嵌套查询中每一条数据都给了一个自增值
解析2:
SET @row_number = 1;
SELECT
pk_id,
ROW_NUMBER,
t.order_month,
DATE_FORMAT( SUBDATE( t.order_month, INTERVAL row_number MONTH ), '%Y-%m' )
FROM
( SELECT ( @row_number := @row_number + 1 ) AS ROW_NUMBER, pk_id, order_month FROM test_month WHERE pk_id = '1' ORDER BY pk_id, order_month ) t
在外层查询中,将每个月份减去内层分配的自增值,可以看出如果是连续缴纳月份,相减后的月份会是相同的,接下来,对处理后的值进行group by操作
完整sql
SELECT
pk_id,
DATE_FORMAT( MAX( t.order_month ), '%Y-%m' ) AS maxMonth,
DATE_FORMAT( MIN( t.order_month ), '%Y-%m' ) AS minMonth,
count( 1 ) AS count
FROM
( SELECT ( @row_number := @row_number + 1 ) AS ROW_NUMBER, pk_id, order_month FROM test_month WHERE pk_id = '1' ORDER BY pk_id, order_month ) t
GROUP BY
pk_id,
DATE_FORMAT( SUBDATE( t.order_month, INTERVAL row_number MONTH ), '%Y%m' )
HAVING
count( 1 ) >1
小结
` 提示:对其他博主的思路进行一次解析,原文链接:
https://blog.csdn.net/weixin_42145354/article/details/126954736?utm_medium=distribute.pc_relevant.none-task-blog-2defaultbaidujs_baidulandingword~default-1-126954736-blog-89866050.235v38pc_relevant_yljh&spm=1001.2101.3001.4242.2&utm_relevant_index=4