mysql 游戏 统计 连续登陆3天 玩家

[上海小胖]

表列：name,lastlogintime(datetime),lastlogouttime(datetime),userid
表名：game_player
找出连续登录3天的用户，
大婶求教啊！
CREATE TABLE `game_player` (
`name` varchar(10) DEFAULT NULL,
`lastlogintime` datetime DEFAULT NULL,
`userid` varchar(10) DEFAULT NULL,
`lastlogouttime` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ；



insert into game_player values ('a','20131001100000','20131001120000','1');
insert into game_player values ('a','20131002100000','20131002120000','1');
insert into game_player values ('a','20131003100000','20131003120000','1');
insert into game_player values ('a','20131004100000','20131004120000','1');
insert into game_player values ('b','20131001100000','20131001120000','2');
insert into game_player values ('b','20131002100000','20131002120000','2');
insert into game_player values ('c','20131003100000','20131003120000','3');
insert into game_player values ('d','20131001100000','20131001120000','4');
insert into game_player values ('d','20131002100000','20131002120000','4');
insert into game_player values ('d','20131004100000','20131004120000','4');


求大神帮助啊！！！

[-Tracy-McGrady-]

标记一下，下班走人。

[上海小胖]

引用 1 楼 yangsh0722 的回复:

标记一下，下班走人。

大神，你要救我啊。。。

[唐诗三百首]

LZ提供的测试数据脚本,数据列好像对应错了喔.

[上海小胖]

引用 3 楼 ap0405140 的回复:

LZ提供的测试数据脚本,数据列好像对应错了喔.

是的。。。不过你们可以忽视的。。。版主解答下吧！跪了。。。

[上海小胖]

怎么没人来啊。。大婶们 都出去溜达了么。。谁来救我。

[上海小胖]

来人啊！！！！！！！

[飞啊子]

;WITH t AS (
	SELECT gp.userid
	,CONVERT(CHAR(8),gp.lastlogintime,112) AS lastlogintime
	,ROW_NUMBER() OVER ( PARTITION BY gp.userid ORDER BY GETDATE()) Rn
	  FROM game_player gp
	GROUP BY gp.userid,CONVERT(CHAR(8),gp.lastlogintime,112)
),t2 AS(
	SELECT t.userid,DATEADD(dd,-t.Rn,t.lastlogintime) lastlogintime
	,COUNT(1) ct
	 FROM t GROUP by t.userid,DATEADD(dd,-t.Rn,t.lastlogintime)
)
 SELECT t2.userId,MAX(ct) ct FROM t2
 WHERE ct>=3
 GROUP BY t2.userId; 

[-Tracy-McGrady-]

@DBA_Huangzj 赶紧回答啊，多好的问题。我帮忙顶顶

[發糞塗牆]

引用 7 楼 yangsh0722 的回复:

@DBA_Huangzj 赶紧回答啊，多好的问题。我帮忙顶顶

现在是唐版主的天下

[-Tracy-McGrady-]

引用 8 楼 DBA_Huangzj 的回复:

引用 7 楼 yangsh0722 的回复:

@DBA_Huangzj 赶紧回答啊，多好的问题。我帮忙顶顶

现在是唐版主的天下

别小气嘛，我知道你有好的方法。

[火拼阿三]

引用 8 楼 DBA_Huangzj 的回复:

引用 7 楼 yangsh0722 的回复:

@DBA_Huangzj 赶紧回答啊，多好的问题。我帮忙顶顶

现在是唐版主的天下

跟着你一起学习发愤图强。。。

[-Tracy-McGrady-]

http://bbs.csdn.net/topics/380253492 给你搜索了一个类似的问题，自己照着改改吧，亲！

[zbdzjx]

凑个热闹，再声明，我可不是“大婶”！！！

select a.[name] from 
game_player a
inner join game_player b on convert(varchar,a.lastlogintime+1,111)=convert(varchar,b.lastlogintime,111)
inner join game_player c on convert(varchar,a.lastlogintime+2,111)=convert(varchar,c.lastlogintime,111)

[上海小胖]

引用 11 楼 yangsh0722 的回复:

http://bbs.csdn.net/topics/380253492 给你搜索了一个类似的问题，自己照着改改吧，亲！

谢谢！感谢你召唤大婶。。。

[上海小胖]

引用 12 楼 zbdzjx 的回复:

凑个热闹，再声明，我可不是“大婶”！！！

select a.[name] from 
game_player a
inner join game_player b on convert(varchar,a.lastlogintime+1,111)=convert(varchar,b.lastlogintime,111)
inner join game_player c on convert(varchar,a.lastlogintime+2,111)=convert(varchar,c.lastlogintime,111)

报错了。 mysql> select a.[name] from -> game_player a -> inner join game_player b on convert(varchar,a.lastlogintime+1,111)=conver t(varchar,b.lastlogintime,111) -> inner join game_player c on convert(varchar,a.lastlogintime+2,111)=conver t(varchar,c.lastlogintime,111) -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[name ] from game_player a inner join game_player b on convert(varchar,a.lastlog' at line 1

[上海小胖]

引用 13 楼 feiazifeiazi 的回复:

;WITH t AS (
	SELECT gp.userid
	,CONVERT(CHAR(8),gp.lastlogintime,112) AS lastlogintime
	,ROW_NUMBER() OVER ( PARTITION BY gp.userid ORDER BY GETDATE()) Rn
	  FROM game_player gp
	GROUP BY gp.userid,CONVERT(CHAR(8),gp.lastlogintime,112)
),t2 AS(
	SELECT t.userid,DATEADD(dd,-t.Rn,t.lastlogintime) lastlogintime
	,COUNT(1) ct
	 FROM t GROUP by t.userid,DATEADD(dd,-t.Rn,t.lastlogintime)
)
 SELECT t2.userId,MAX(ct) ct FROM t2
 WHERE ct>=3
 GROUP BY t2.userId; 

报错了。 mysql> ;WITH t AS ( ERROR: No query specified -> SELECT gp.userid -> ,CONVERT(CHAR(8),gp.lastlogintime,112) AS lastlogintime -> ,ROW_NUMBER() OVER ( PARTITION BY gp.userid ORDER BY GETDATE()) Rn -> FROM game_player gp -> GROUP BY gp.userid,CONVERT(CHAR(8),gp.lastlogintime,112) -> ),t2 AS( -> SELECT t.userid,DATEADD(dd,-t.Rn,t.lastlogintime) lastlogintime -> ,COUNT(1) ct -> FROM t GROUP by t.userid,DATEADD(dd,-t.Rn,t.lastlogintime) -> ) -> SELECT t2.userId,MAX(ct) ct FROM t2 -> WHERE ct>=3 -> GROUP BY t2.userId; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't AS ( SELECT gp.userid ,CONVERT(CHAR(8),gp.lastlogintime,112) AS lastlo' at line 1

[zbdzjx]

引用 15 楼 u012365828 的回复:

[quote=引用 12 楼 zbdzjx 的回复:] 凑个热闹，再声明，我可不是“大婶”！！！

select a.[name] from 
game_player a
inner join game_player b on convert(varchar,a.lastlogintime+1,111)=convert(varchar,b.lastlogintime,111)
inner join game_player c on convert(varchar,a.lastlogintime+2,111)=convert(varchar,c.lastlogintime,111)

报错了。 mysql> select a.[name] from -> game_player a -> inner join game_player b on convert(varchar,a.lastlogintime+1,111)=conver t(varchar,b.lastlogintime,111) -> inner join game_player c on convert(varchar,a.lastlogintime+2,111)=conver t(varchar,c.lastlogintime,111) -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[name ] from game_player a inner join game_player b on convert(varchar,a.lastlog' at line 1[/quote] 把name两边的[]去掉再试试看。

[上海小胖]

不行。。。要吐血了。。。