求改hql hql 语句不支持from后面直接跟子查询表

[wbsmm]

我想统计该生在该章节的 *做题总数* 中的排行

select count(1) from (select count(*) as con from test_log where chapter_id=12 group by user_id) as a where a.con>= (select count(*) from test_log where user_id=3 and chapter_id=12);
在mysql数据库中能够查询到结果


在hibernate中，return (Integer)this.getHibernateTemplate().find("select count(*) from (select count(1) as con from TestLog where chapterId="+chapterId+" group by userId) as a where a.con>= (select count(*) from TestLog where userId="+userId+" and chapterId="+chapterId+")").get(0);

报出错误信息：
[ERROR]http-8082-4-org.apache.catalina.core.ContainerBase.[Catalina].[localhost].[/superteacher1.0].[action]-Servlet.service() for servlet action threw exception
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 22 [select count(*) from (select count(1) as con from hibernate.dao.TestLog where chapterId=2 group by userId) as a where a.con>= (select count(*) from hibernate.dao.TestLog where userId=9 and chapterId=2)]


好像是不识别from后面是个子查询表，哪位高手能把我这个hql语句改改~~~

[wbsmm]

大家帮帮我！感激不尽啊~~~~

[mianfeidog]

我发现两个查询count(1) , count(*) 顺序不一样。

[xdop]

hql不支持from后面直接带子查询，这要从sql本身改造。
想想能否构造一个sql满足需求，又绕过这一限制的语句。

[wbsmm]

帮帮忙，想想~~谢谢大家了~~~~

[fredy1111]

我也没在hql里面写过嵌套查询。其实既然有java了不用再局限在sql语句里啊，用java辅助也可以嘛。
直接传语句select count(*),user_id from test_log where chapter_id=12 group by user_id order by count(*) desc;
然后出来之后就是一个list里面两个元素count(*)和user_id。遍历list当中的user_id，这个user第几个被遍历到，就是第几名了。

[wbsmm]

谢谢这位仁兄了！