hibernate子查询的问题

Question

  我的HQL语句是这样写的:
    "SELECT a.deviceType,a.typeCount,b.newCount FROM "
+ "((SELECT id,COUNT(deviceType) as typeCount,deviceType FROM Device  GROUP BY deviceType) AS a"+
",(SELECT id, COUNT(devicetype) as newCount,deviceType FROM Device WHERE modifyTime>'2011-04-06' GROUP BY deviceType) AS b)" + "WHERE a.deviceType = b.deviceType"; 
    执行query = createQuery(hql);
         list = query.list();抛异常 请问这样的SQL语句有什么问题？

Answer 1

异常原因:unexpected token: ( near line 1, column 49 [SELECT a.deviceType,a.typeCount,b.newCount FROM ((SELECT id,COUNT(deviceType) as typeCount,deviceType FROM com.eshore.est.entity.datamgr.Device  GROUP BY deviceType) AS a,(SELECT id, COUNT(devicetype) as newCount,deviceType FROM com.eshore.est.entity.datamgr.Device WHERE modifyTime>'2011-04-06' GROUP BY deviceType) AS b)WHERE a.deviceType = b.deviceType]
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 49 [SELECT a.deviceType,a.typeCount,b.newCount FROM ((SELECT id,COUNT(deviceType) as typeCount,deviceType FROM com.eshore.est.entity.datamgr.Device  GROUP BY deviceType) AS a,(SELECT id, COUNT(devicetype) as newCount,deviceType FROM com.eshore.est.entity.datamgr.Device WHERE modifyTime>'2011-04-06' GROUP BY deviceType) AS b)WHERE a.deviceType = b.deviceType]
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:284)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:182)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1651)
at org.springside.modules.orm.hibernate.SimpleHibernateDao.createQuery(SimpleHibernateDao.java:238)
at com.eshore.est.dao.datamgr.DeviceDao.queryStatisticOfTools(DeviceDao.java:48)
at com.eshore.est.service.datamgr.DeviceManager.queryStatisticOfTools(DeviceManager.java:64)
at com.eshore.est.service.datamgr.DeviceManager$$FastClassByCGLIB$$eb6ba43d.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:191)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
at com.eshore.est.service.datamgr.DeviceManager$$EnhancerByCGLIB$$7d1f669c.queryStatisticOfTools(<generated>)
at com.eshore.est.web.datamgr.DeviceAction.statisticTools(DeviceAction.java:119)

Answer 2

WHERE modifyTime > '2011-04-06' 时间类型的不可以和字符串类型的做比较，作如下修改WHERE modifyTime > to_date('2011-04-06','yyyy-mm-dd')

Answer 3

还是不行，好像HQL语句不能查询 套链子查询这样查询