我需要统计每月的销售金额,表名:Day, 表结构和示例数据如下:(sqlserver2000)
d_id, d_date, d_money
1, 2008-01-01, 100
2, 2008-01-01, 2003, 2008-02-01, 3004, 2008-03-01, 400
5, 2008-03-01, 500
...想实现的效果:
2008-01,300
2008-02,300
2008-03,900我在 hibernate 的 HQL 语句中使用了:下面的 SQL
SELECT convert(varchar(7), d_date, 120) as a, sum(d_money) as b from Day group by convert(varchar(7), d_date, 120) order by a编译没错,执行的时候抱错如下:
严重: Servlet.service() for servlet default threw exception
java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.MethodNode
+-[METHOD_CALL] MethodNode: '('
| +-[METHOD_NAME] IdentNode: 'convert' {originalText=convert}
| \-[EXPR_LIST] SqlNode: 'exprList'
| +-[METHOD_CALL] MethodNode: '('
| | +-[METHOD_NAME] IdentNode: 'varchar' {originalText=varchar}
| | \-[EXPR_LIST] SqlNode: 'exprList'
| | \-[NUM_INT] LiteralNode: '7'
| +-[IDENT] IdentNode: 'd_date' {originalText=d_date}
| \-[NUM_INT] LiteralNode: '120' at org.hibernate.hql.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:145)
at org.hibernate.hql.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:705)
at org.hibernate.hql.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:529)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:645)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:228)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
at org.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.java:832)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:362)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:830)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:822)
d_id, d_date, d_money
1, 2008-01-01, 100
2, 2008-01-01, 2003, 2008-02-01, 3004, 2008-03-01, 400
5, 2008-03-01, 500
...想实现的效果:
2008-01,300
2008-02,300
2008-03,900我在 hibernate 的 HQL 语句中使用了:下面的 SQL
SELECT convert(varchar(7), d_date, 120) as a, sum(d_money) as b from Day group by convert(varchar(7), d_date, 120) order by a编译没错,执行的时候抱错如下:
严重: Servlet.service() for servlet default threw exception
java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.MethodNode
+-[METHOD_CALL] MethodNode: '('
| +-[METHOD_NAME] IdentNode: 'convert' {originalText=convert}
| \-[EXPR_LIST] SqlNode: 'exprList'
| +-[METHOD_CALL] MethodNode: '('
| | +-[METHOD_NAME] IdentNode: 'varchar' {originalText=varchar}
| | \-[EXPR_LIST] SqlNode: 'exprList'
| | \-[NUM_INT] LiteralNode: '7'
| +-[IDENT] IdentNode: 'd_date' {originalText=d_date}
| \-[NUM_INT] LiteralNode: '120' at org.hibernate.hql.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:145)
at org.hibernate.hql.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:705)
at org.hibernate.hql.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:529)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:645)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:228)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
at org.springframework.orm.hibernate3.HibernateTemplate$29.doInHibernate(HibernateTemplate.java:832)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:362)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:830)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:822)
2008-01,300
2008-02,300
2008-03,900
return this.getHibernateTemplate().find(hql);
return (Collection) getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException {
return session
.createSQLQuery("SELECT convert(varchar(7), d_date, 120) as a, sum(d_money) as b from Day group by convert(varchar(7), d_date, 120) order by a").list();
}
});
}