连续登录统计 Mysql

SELECT
	MIN(StartTime) AS StartTime,
	MAX(EndTime) AS EndTime,
	MAX(ContinueCount) AS ContinueCount
FROM
	(
		SELECT
			StartId,
			StartTime,
			EndId,
			EndTime,
			Num,
			@count := CASE
		WHEN (Num = 1) THEN
			@count :=@count + 1
		ELSE
			@count := 0
		END AS ContinueCount
		FROM
			(
				SELECT
					a.Id AS StartId,
					a.AddOn AS StartTime,
					b.Id AS EndId,
					b.AddOn AS EndTime,
					DATEDIFF(a.AddOn , b.AddOn ) AS Num
				FROM
					(
						SELECT
							@startnum :=@startnum + 1 AS RowNum,
							Tab.Id,
							Tab.AddOn 
						FROM
							(
								SELECT
									Id,
									DATE(AddOn) AS AddOn 
								FROM
									table
								GROUP BY
									DATE(AddOn)
								ORDER BY
									AddOn DESC
							) AS Tab,
							(SELECT @startnum := 0) starttemp
					) AS a
				INNER JOIN (
					SELECT
						@endnum :=@endnum + 1 AS RowNum,
						Tab.Id,
						Tab.AddOn 
					FROM
						(
							SELECT
								Id,
								DATE(AddOn) AS AddOn 
							FROM
								table
							GROUP BY
								DATE(AddOn)
							ORDER BY
								AddOn DESC
						) AS Tab,
						(SELECT @endnum := 0) endtemp
				) AS b ON a.RowNum + 1 = b.RowNum
			) AS source,
			(SELECT @count := 0) count
	) AS s

table属性:

Id Guid

AddOn datetime 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值