公司要求代码优化,原来的代码是用HQL写的,为了提高效率,我就使用SQL写
原HQL语句如下:
select entity.orderCode,entity.materielCode,entity.materielName,entity.partnerCode, sum(entity.inEAAmount), sum(entity.outEAAmount),sum(entity.reqEAAmount),sum(entity.debugEAAmount), sum(entity.returnEAAmount) from RequireAccountingSimpleEntity entity , MaterielSimpleEntity c where entity.bizType = 'FLPRODUCTION' and entity.materielId = c.id and entity.drawType = 'FLPRO' group by entity.orderCode,entity.materielCode,entity.materielName,entity.partnerCode
经过我的修改后的SQL语句如下:
select entity.orderCode,entity.partnerCode,c.materielCode,c.cnName, sum(entity.inEAAmount),sum(entity.outEAAmount),sum(entity.reqEAAmount),sum(entity.debugEAAmount),sum(entity.returnEAAmount) from Wms_ReqAccounting entity inner join Bas_MaterielBaseInfo c on entity.materielId = c.id where entity.bizType = 'FLPRODUCTION' and entity.drawType = 'FLPRO' group by entity.orderCode,entity.partnerCode,c.materielCode,c.cnName
但发现改成SQL语句后经SUM汇总的数据对不上:
SQL语句中有5个SUM,如果其中的一个SUM语句的值为NULL,则后面每个SUM的无论有没有值,结果都为NULL。这是Hibernate的SQL语句查询会有的问题,但HQL查询却不会有。