问题描述
StringBuilder sb = new StringBuilder("select obj.eid, obj.employee.ename, obj.employee.enumber, count(obj.isBeLate) as 迟到次数, count(case when obj.lateReson='迟到' then obj.lateReson end) as 迟到");sb.append(",count(case when obj.lateReson='旷工' then obj.lateReson end) as 旷工,");sb.append("count(case when obj.lateReson='病假' then obj.lateReson end) as 病假,");sb.append("count(case when obj.lateReson='事假' then obj.lateReson end) as 事假,");sb.append("count(case when obj.lateReson='丧假' then obj.lateReson end) as 丧假,");sb.append("count(case when obj.lateReson='婚假' then obj.lateReson end) as 婚假,");sb.append("count(case when obj.lateReson='产假' then obj.lateReson end) as 产假,");sb.append("count(case when obj.lateReson='公假' then obj.lateReson end) as 公假,");sb.append("count(case when obj.lateReson='年假' then obj.lateReson end) as 年假,");sb.append("sum(points) as 扣分,");sb.append("sum(fine) as 罚金 ");sb.append(" from Attendance obj where month(obj.modifyTime) =");sb.append("datepart(mm,getdate()) and obj.isBeLate = '是' group by obj.eid, obj.employee.ename, obj.employee.enumber");getSession().createQuery(sb.toString()).list();上面的代码就不解释了， Attendance employee 两个对象一对多关联关系。 <many-to-one name="employee" lazy="false" cascade="save-update" class="com.crm.employee.domain.Employee" fetch="select"><column name="eid"></column></many-to-one><set name="setAttendance" table="attendance" cascade="all" inverse="true"><key column="eid" /><one-to-many class="com.crm.employee.domain.Attendance" /></set>此sql无法执行，但是在sql中是可以正确执行的，求解。。。。
解决方案
hibernate 不支持 count + case，改成 sum +case即可