求一个mysql语句,求高手解答

我用的是mysql数据库，有两张表：
CREATE TABLE users (
u_id INT,
u_name VARCHAR(10)
);
CREATE TABLE login_record(
id INT,
u_id INT,
logintime, -- 登陆时间
CONSTRAINT fk_lr_u_id FOREIGN KEY(u_id) REFERENCES users(u_id)
);

我的要求是，查出至今连续登陆N天的用户或者指定时间开始连续登陆N天的用户，注意是连续登陆的。求高手解答。

http://www.itpub.net/forum.php?mod=viewthread&tid=1587647&highlight=

select a.username
from logtable a,logtable b,logtable c
where a.time=b.time + 1 and b.time=c.time+1
and a.action='loging' and b.action='loging' and c.action='loging'
and a.usernaem=b.username and a.username=c.username

总得有个登出时间吧，你这表中光一个登录时间，怎么算连续登录天数啊

一个笨方法，可以试试
SELECT a.u_id FROM login_record a
JOIN login_record b ON a.u_id=b.u_id AND DATEDIFF(a.logintime,b.logintime) = 1
JOIN login_record c ON a.u_id=c.u_id AND b.u_id=c.u_id AND DATEDIFF(a.logintime,c.logintime) = 2 AND DATEDIFF(b.logintime,c.logintime) = 1
WHERE
a.logintime >= '2017-10-11 00:00:00'
GROUP BY a.u_id

 select u_name from user where u_id in(
     select 
            u_id 
    from 
        login_record 
    where 
        date_format(now(),'%Y-%m-%d') =date_format(logintime,'%Y-%m-%d) 
    or 
        date_format(date_add(now(),interval -1 day),'%Y-%m-%d') = date_format(logintime,'%Y-%m-%d) 
    or 
        date_format(date_add(now(),interval -2 day),'%Y-%m-%d') = date_format(logintime,'%Y-%m-%d) 
    group by (u_id) 
    having count(u_id)>3
 )

我这个sql的思想主要是找出来N天前是哪些用户，然后看N天内有没有每天都登录，我没有测试，但我觉得应该是满足你的需求的，你可以测试一下，如果有问题可能稍微修正一下就行。
SELECT c.u_id,b.u_name FROM (
SELECT b.u_id,count(b.u_id) n from (
SELECT DISTINCT ll.u_id,date_format(ll.logintime, '%Y%m%d')
FROM login_record ll,
(SELECT l.id,l.u_id,l.logintime FROM login_record l where datediff(now(),l.logintime)=10) a
where ll.logintime>a.logintime) b
group BY b.u_id) c, users u
WHERE c.u_id=u.u_id
and c.n=10;

以前看到过一个贴子，是查询的表jjdb中所有的jjdwbh最大连续出现天数，下面是我改成我自己需要的：
SELECT * FROM
(SELECT * FROM(
SELECT jjdwbh,max(days) lianxu_days,min(login_day) start_date,max(login_day) end_date FROM(
SELECT jjdwbh,@cont_day := (
CASE
WHEN (
@last_uid = jjdwbh
AND DATEDIFF(bjsj, @last_dt) = 1
) THEN
(@cont_day + 1)
WHEN (
@last_uid = jjdwbh
AND DATEDIFF(bjsj, @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 := jjdwbh,@last_dt := bjsj login_day
FROM
(SELECT jjdwbh,DATE(bjsj) bjsj FROM b_jjdb WHERE jjdwbh != 0 ORDER BY jjdwbh,bjsj) AS t,
(SELECT @last_uid := '',@last_dt := '',@cont_ix := 0,@cont_day := 0) AS t1
) AS t2
GROUP BY jjdwbh,cont_ix HAVING lianxu_days>5
)
as tmp ORDER BY lianxu_days DESC
)
ntmp GROUP BY jjdwbh;

我也是自己慢慢理解的，希望对你也有帮助

老哥，你这个写法很强，不过，我自己研究了另一种算法，也一样可以解决这种连续登陆的，有兴趣可以一起探讨。

select d.id,d.firs_lianxu_time,max(d.lianxu_amount) lianxu_amount from (
select c.id,c.first_lianxu_time,count(distinct c.rn) lianxu_amount from (
select b.*,dateadd(dd,-b.rn,b.create_time) first_lianxu_time from (
SELECT a.create_time,a.id,dense_rank()over(partition by a.id order by a.create_time ) rn from (
select
cast(cast(create_time as varchar(10)) as date) create_time
,id
from table_test
where create_time>='2017-10-01'
) a
) b
) c
group by c.id,c.first_lianxu_time
) d
group by d.id,d.first_lianxu_time