code=Java]public List<LampInfo> findLampsBoxID(int boxID, int boxgruop) {
List<LampInfo> list = null;
Session session = this.getSession();
try {
String sql = "FROM LampInfo "
+ " WHERE LampID IN(SELECT max(LampID) FROM LampInfo"
+ " WHERE IsDoubleLamp='Y' and boxID= " + boxID + " and boxgruop= " + boxgruop + " "
+ " GROUP BY SUBSTR(LAMPNO,1,LENGTH(LampNo)-1))"
+ " UNION ALL FROM LampInfo WHERE NVL(IsDoubleLamp,'')<>'Y' and "
+ " and boxID= " + boxID + " and boxgruop= " + boxgruop + " order by groupnum ";
Query query = session.createQuery(sql);
list = query.list();
return list;
} catch (Exception e) {
e.printStackTrace();
return null;
}finally {
session.close();
} }
[/code]LampID boxID boxgruop groupnum 都为LampInfo的字段程序报错No data type for node .. AggregateNode: 'max' (SSH框架,SQL语句单独测试过没错,关键在SSH框架下MAX MIN等聚合函数会报错
List<LampInfo> list = null;
Session session = this.getSession();
try {
String sql = "FROM LampInfo "
+ " WHERE LampID IN(SELECT max(LampID) FROM LampInfo"
+ " WHERE IsDoubleLamp='Y' and boxID= " + boxID + " and boxgruop= " + boxgruop + " "
+ " GROUP BY SUBSTR(LAMPNO,1,LENGTH(LampNo)-1))"
+ " UNION ALL FROM LampInfo WHERE NVL(IsDoubleLamp,'')<>'Y' and "
+ " and boxID= " + boxID + " and boxgruop= " + boxgruop + " order by groupnum ";
Query query = session.createQuery(sql);
list = query.list();
return list;
} catch (Exception e) {
e.printStackTrace();
return null;
}finally {
session.close();
} }
[/code]LampID boxID boxgruop groupnum 都为LampInfo的字段程序报错No data type for node .. AggregateNode: 'max' (SSH框架,SQL语句单独测试过没错,关键在SSH框架下MAX MIN等聚合函数会报错
解决方案 »
- JAVA计算水库库容
- Could not get constructor for org.hibernate.persister.entity.SingleTableEntityPe
- hibernate one-to-one 引用字典表级联更新的问题
- 关于java网络聊天室~~~
- struts2 中的超链接怎么设置可以只点击一次?
- struts2.0 在ACTION中怎么调用struts.properties资源文件?
- 为什么访问不了Resin的官方网站?
- Hibernate 主键问题
- 请问能否用JDBC去检测表是否存在?
- 100分啊!!高手都到哪里去了
- 数据重复问题(java)
- 一道关于this和super的面试题
一 GROUP BY 要跟聚合函数配合用
二 UNION ALL (UNION)跟 ORDER BY 一起用会报错 要再加一层用括号括起来原生 SQL 要用 SQLQuery query = session.createSQLQuery(sql)
我开始贴错了HQL的
因为HQL不支持 UNION ALL (UNION)故用SQL
Session session = this.getSession();
try {
String sql = " select * FROM (SELECT * from LampInfo WHERE LampID IN(SELECT MIN(LampID) FROM LampInfo "
+ " WHERE IsDoubleLamp='Y' and boxID = " + boxID + " and boxgruop = " +boxgruop + " "
+ " GROUP BY SUBSTR(LAMPNO,1,LENGTH(LampNo)-1)) ORDER BY LampID) t1 "
+ " UNION ALL select * FROM (SELECT * from LampInfo WHERE IsDoubleLamp<>'Y' "
+ " and boxID = " + boxID + " and boxgruop = " + boxgruop + " ORDER BY LampID ) t2 ";
SQLQuery query = session.createSQLQuery(sql).addEntity("LampInfo",LampInfo.class);
List<LampInfo> list = query.list(); return list;
} catch (Exception e) {
e.printStackTrace();
return null;
}finally {
session.close();
} }