原帖:http://topic.csdn.net/u/20110414/11/7d4aefc4-6e08-48ac-892b-26bd482625fa.html?1138hibernate 里 的HQL 写法如何表达这一段SQL查询表 tb列 id num dateSQL语句:select *,(select sum(num) from tb where id = t.id and year(date) = year(t.date) and month(date) = month(t.date) and date <= t.date) as csum
from tb t
from tb t
你的实体类和hbm.xml是怎么配置的?
表已经给出了表 tb列 id num dateid是可以重复的,当然是为了简化问题!第二个实体类的关系,就是这个简单表的实体,可以看作是 tb第三,配置什么的暂不管, HQL里写 from tb 肯定会有的我知道,如何实现上面问题里的虚拟新增的那一列!在实体里放一个虚拟列试过了,是行不通的!
例如:
select a from A a,(select b from B b,C c where b.id=c.id) d where a.id=d.id;
改为
select a from A a where a.id in (select b.id from B b,C c where b.id=c.id);
你的B表应该有主键ID吧
你可以这样写select t.*,sum(num)from tb t inner join tb t1 on t1.id = t.id and year(t1.date) = year(t.date) and month(t1.date) = month(t.date) and t1.date <= t.date) group by 主键ID
/*HQL有点问题,现在是写成这个样子,运行过不去!*//*DAO 层方法!*/
public ArrayList getEmCarExpenseList(int owner){
ArrayList arrayPage = new ArrayList();
String sql = "select *,(select sum(isnull(repair,0)) from EM_CarExpenses where owner = t.owner and licenseNumber = t.licenseNumber "
+" and year(createTime) = year(t.createTime) and month(createTime) = month(t.createTime) and createTime <= t.createTime) as csum "
+" from EM_CarExpenses t where owner = ?";
Session session = this.getSessionFactory().openSession();
Query query = session.createQuery(sql); -- 在这里异常
query.setInteger(0,owner);
arrayPage=(ArrayList)query.list();
session.close();
return arrayPage;
}
/*Action 层调用*/List<Object> list = expensesBiz.getEmCarExpenseList(com.getSmCompany().getId());
request.setAttribute("carExpensesList", list);
return mapping.findForward("carExpensesList");
直接去执行SQL吧