写法一:
SELECT
userid,
min( login_time ) min_date,
max( login_time ) max_date,
count( 1 ) AS day_count
FROM
(
SELECT
b.*,
date_add( login_time, INTERVAL - IF ( @group_str = userid, @num := @num + 1, @num := 1 ) DAY ) AS login,
@group_str : userid AS temp
FROM
b
CROSS JOIN ( SELECT @num := 0, @group_str =- 1 ) t
ORDER BY
b.userid,
login_time
) t
GROUP BY
userid,
login;
写法二:
SELECT *
FROM (SELECT *
FROM (
SELECT
uid,
max(days) lianxu_days,
min(login_day) start_date,
max(login_day) end_date
FROM (SELECT uid,
@cont_day :=
(CASE
WHEN (@last_uid = uid AND DATEDIFF(ptime, @last_dt) = 1)
THEN
(@cont_day + 1)
WHEN (@last_uid = uid AND DATEDIFF(ptime, @last_dt) < 1)
THEN
(@cont_day + 0)
ELSE
1
END) AS days,
(@cont_ix := (@cont_ix + IF(@cont_day = 1, 1, 0))) AS cont_ix,
@last_uid := uid,
@last_dt := ptime login_day
FROM (SELECT
uid,
FROM_UNIXTIME(push_time,'%Y-%m-%d') ptime
FROM qh_manito_push_orders
WHERE uid != 0
ORDER BY uid, ptime) AS t,
(SELECT
@last_uid := '',
@last_dt := '',
@cont_ix := 0,
@cont_day := 0) AS t1
) AS t2
GROUP BY uid, cont_ix
) tmp
ORDER BY lianxu_days DESC) ntmp
GROUP BY uid
你可以像疯狗那样对周围的一切愤愤不平,你可以诅咒命运,但是等到最后一刻到来之时,你还得平静的放手而去。