下面的mysql sql如何写：报错

每行计算出3个辅助列，距离最早那天的天数，比本行时间早的行数，缺少的天数（距离最早那天的天数-比本行时间早的行数）。

连续登陆的那几天缺少的天数是相同的，按用户和缺少的天数统计一下就是连续登陆的天数。

按这个思路简单写下SQL：

SELECT
	uid,
	min_date_diff - before_date_count miss_date_count,
	count(*)
FROM
	(
		SELECT
			user_time.*, datediff(
				user_time.time,
				user_min_time.min_time
			) + 1 min_date_diff,
			(
				SELECT
					count(*)
				FROM
					user_time i
				WHERE
					i.uid = user_time.uid
				AND i.time <= user_time.time
			) before_date_count
		FROM
			user_time
		INNER JOIN (
			SELECT
				uid,
				min(time) min_time
			FROM
				user_time
			GROUP BY
				uid
		) user_min_time ON user_min_time.uid = user_time.uid
	) user_time_stat
GROUP BY
	uid,
	miss_date_count

结果：

uid	miss_date_count	count(*)
1	0	4
1	5	2
2	0	3

######我觉得这种记录之所以有需求是设计问题，连续登陆应该在每天都计算，如果发现最近一次登陆不连续，马上覆盖重算，比这么计算简单的多######我之前就是这样搞得，每天第一次登陆+1,如果最近的一个登陆不是昨天，从0开始计算，方便的很。根本不需要像LZ那样！######我擦，感觉太难了，我想想###### 已找到答、案

http://www.oschina.net/question/573517_118821
谢谢上面回答，吐槽一下，发完贴然后居然找不到该贴了，隔了一天，今天才显示出来，不知道怎么回事。 ######CREATE TABLE countLine AS

SELECT 1 AS id, 1 AS uid, 20150101 AS TIME UNION ALL

SELECT 2 AS id, 1 AS uid, 20150102 AS TIME UNION ALL

SELECT 3 AS id, 2 AS uid, 20150101 AS TIME UNION ALL

SELECT 4 AS id, 1 AS uid, 20150103 AS TIME UNION ALL

SELECT 5 AS id, 2 AS uid, 20150102 AS TIME UNION ALL

SELECT 6 AS id, 2 AS uid, 20150103 AS TIME UNION ALL

SELECT 7 AS id, 1 AS uid, 20150104 AS TIME UNION ALL

SELECT 8 AS id, 1 AS uid, 20150110 AS TIME UNION ALL

SELECT 9 AS id, 1 AS uid, 20150111 AS TIME;

--查询语句

SELECT uid,COUNT(1) AS count_time FROM
(SELECT id,uid,TIME,TIME-IF(@uid=uid,(@rn:=@rn+1),@rn:=1) AS diff ,@uid :=uid FROM countLine e,(SELECT @rn:=0,@uid='',@rnx:=0) c ORDER BY uid,TIME) a GROUP BY uid,diff;

结果

   uid  count_time   ------  ------------      1             4      1             2      2             3