当前负责的项目使用的是hibernate，而“：”是hibernate的一个占位符，作为预编译使用的，
select tmp.pid from
(select pid, loginTime, @i := @i + 1 i from gms_record_login_game, (select @i := 0) r order by pid, loginTime) tmp
LEFT JOIN
(select pid, loginTime, @j := @j + 1 k from gms_record_login_game, (select @j := 0) r1 order by pid, loginTime) tmp2
on tmp.i + 1 = tmp2.k and tmp.pid = tmp2.pid
WHERE TIMESTAMPDIFF(DAY,DATE_FORMAT(tmp.loginTime,'%Y-%m-%d'),DATE_FORMAT(tmp2.loginTime,'%Y-%m-%d'))>3
GROUP BY tmp.pid
使用HQL查询时报错：Query query = session.createQuery(HQL);
QueryException: unexpected char: '@'
原因：HQL不支持这种查询
解决方案：使用原生SQL查询时报错：SQLQuery query = session.createSQLQuery(SQL);
Space is not allowed after parameter prefix ':'
原因：这是hibernate3.X包之下的一个bug,(参照 id=41741)在hibernate4.X中已经修复。
解决方案：需要对双冒号进行转义，在使用双反斜杠进行转义
select tmp.pid from
(select pid, loginTime, @i \\:= @i + 1 i from gms_record_login_game, (select @i \\:= 0) r order by pid, loginTime) tmp
LEFT JOIN
(select pid, loginTime, @j \\:= @j + 1 k from gms_record_login_game, (select @j \\:= 0) r1 order by pid, loginTime) tmp2
on tmp.i + 1 = tmp2.k and tmp.pid = tmp2.pid
WHERE TIMESTAMPDIFF(DAY,DATE_FORMAT(tmp.loginTime,'%Y-%m-%d'),DATE_FORMAT(tmp2.loginTime,'%Y-%m-%d'))>3
GROUP BY tmp.pid
重点来了修改后依旧没有解决！！！！
后来在*看到有人回复
Another solution for those of us who can't make the jump to Hibernate 4.1.3.
Simply use /*'*/:=/*'*/ inside the query. Hibernate code treats everything between ' as a string (ignores it). MySQL on the other hand will ignore everything inside a blockquote and will evaluate the whole expression to an assignement operator.
I know it's quick and dirty, but it get's the job done without stored procedures, interceptors etc.
修改后
select tmp.pid from
(select pid, loginTime, @i /*'*/:=/*'*/ @i + 1 i from gms_record_login_game, (select @i /*'*/:=/*'*/ 0) r order by pid, loginTime) tmp
LEFT JOIN
(select pid, loginTime, @j /*'*/:=/*'*/ @j + 1 k from gms_record_login_game, (select @j /*'*/:=/*'*/ 0) r1 order by pid, loginTime) tmp2
on tmp.i + 1 = tmp2.k and tmp.pid = tmp2.pid
WHERE TIMESTAMPDIFF(DAY,DATE_FORMAT(tmp.loginTime,'%Y-%m-%d'),DATE_FORMAT(tmp2.loginTime,'%Y-%m-%d'))>3
GROUP BY tmp.pid
问题解决