msql计算连续签到天数

最新推荐文章于 2023-12-20 17:23:21 发布

zlfjavahome

最新推荐文章于 2023-12-20 17:23:21 发布

阅读量493

点赞数

文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/zlfjavahome/article/details/127884334

版权

sql语句如下：

SELECT
count( 1 ) 
FROM
(
SELECT
date_sub( a.create_time, INTERVAL 1 DAY ) signDate,
( @i := DATE_ADD( @i, INTERVAL - 1 DAY ) ) today 
FROM
( SELECT create_time FROM zb_sign_in_record WHERE user_id = '1389750439605604353' ORDER BY create_time DESC ) a
INNER JOIN (
SELECT
@i := max( create_time ) AS signMax 
FROM
zb_sign_in_record
WHERE
user_id = '1389750439605604353' 
AND (
TO_DAYS( create_time ) = TO_DAYS( curdate( ) ) 
OR TO_DAYS( create_time ) = TO_DAYS( DATE_ADD( curdate( ), INTERVAL - 1 DAY ) ) 
) 
) b 
WHERE
b.signMax IS NOT NULL 
AND TO_DAYS( DATE_ADD( @i, INTERVAL - 1 DAY ) ) = TO_DAYS( date_sub( a.create_time, INTERVAL 1 DAY ) ) 
) c