关于 分组查询的分页，统计

/**
     * 获得关封申请单信息加载子表的记录(按条件)
     */
    public List findFptAppItemsByCondition(Map params, int index, int length, String inOutFlag) {
        String hql = "select sc.code,sc.name,ah.appNo,ai.hsNo,ai.hsCode,ai.hsName,ai.hsModel,hsU.name,sum(ai.qty), "
                + " COALESCE((select sum(bi1.qty) as indentureAmount from FptBillItem bi1 left join bi1.fptBillHead bh1  "
                + " where ah.appNo = bh1.appNo and bh1.company= ? " + " and bi1.inOutFlag = '" + inOutFlag
                + "' and bh1.inOutFlag = '" + inOutFlag + "' and bi1.hsNo = ai.hsNo and bh1.sysType = '2' "
                + "  and bh1.appState = '" + DeclareStatus.PROCESS_EXE + "' "
                + (inOutFlag.equals(FptInOutFlag.IN) ? " and ah.inEmsNo = bi1.inEmsNo " : " and ah.outEmsNo = bh1.outEmsNo ") + "),0.0),"
                + "  COALESCE((select sum(bi2.qty) as returnAmount from FptBillItem bi2 left join bi2.fptBillHead bh2  "
                + " where ah.appNo = bh2.appNo and bh2.company= ? " + " and bi2.inOutFlag = '" + inOutFlag
                + "' and bh2.inOutFlag = '" + inOutFlag + "' and bi2.hsNo = ai.hsNo and bh2.sysType = '3' "
                + "  and bh2.appState = '" + DeclareStatus.PROCESS_EXE + "' "
                + (inOutFlag.equals(FptInOutFlag.IN) ? " and ah.inEmsNo = bi2.inEmsNo " : " and ah.outEmsNo = bh2.outEmsNo ") + "),0.0),"
                + " ah.contractNo,ah.inEmsNo,ah.outEmsNo,"
                + (inOutFlag.equals(FptInOutFlag.IN) ? "ah.inDeclareDate " : "ah.outDeclareDate ")
                + " from FptAppItem ai left join ai.hsUnit hsU left join ai.fptAppHead ah left join ah.scmCoc sc "
                + " where ah.company=? " + " and ah.inOutFlag = '" + inOutFlag + "' and ai.inOutFlag = '" + inOutFlag + "' ";
        List conditions = new ArrayList();
        conditions.add(CmpServerUtils.getCompany());
        conditions.add(CmpServerUtils.getCompany());
        conditions.add(CmpServerUtils.getCompany());
        if (params.get("inOutFlag") != null) {
            hql += " and ah.inOutFlag = ? ";
            conditions.add(params.get("inOutFlag"));
        }
        if (params.get("scmCoc") != null) {
            hql += " and ah.scmCoc = ? ";
            conditions.add(params.get("scmCoc"));
        }
        if (params.get("inBeginDate") != null) {
            hql += " and ah.inDeclareDate >= ? ";
            conditions.add(params.get("inBeginDate"));
        }
        if (params.get("inEndDate") != null) {
            hql += " and ah.inDeclareDate <= ? ";
            conditions.add(params.get("inEndDate"));
        }
        if (params.get("outBeginDate") != null) {
            hql += " and ah.outDeclareDate >= ? ";
            conditions.add(params.get("outBeginDate"));
        }
        if (params.get("outEndDate") != null) {
            hql += " and ah.outDeclareDate <= ? ";
            conditions.add(params.get("outEndDate"));
        }
        if (params.get("appNo") != null) {
            hql += " and ah.appNo = ? ";
            conditions.add(params.get("appNo"));
        }
        if (params.get("inEmsNo") != null) {
            hql += " and ah.inEmsNo = ? ";
            conditions.add(params.get("inEmsNo"));
        }
        if (params.get("emsNo") != null) {
            hql += " and ah.outEmsNo = ? ";
            conditions.add(params.get("emsNo"));
        }
        if (params.get("contrNo") != null) {
            hql += " and ah.contractNo = ? ";
            conditions.add(params.get("contrNo"));
        }
        hql += " group by ah.id,ai.hsNo,sc.code,sc.name,ah.appNo,ai.hsCode,ai.hsName,ai.hsModel,hsU.name,"
                + (inOutFlag.equals(FptInOutFlag.IN) ? "ah.inDeclareDate " : "ah.outDeclareDate ");
        return this.findPageList(hql, index, length, conditions.toArray());
    }

   /**
     * 获得关封申请单信息加载子表的记录数(按条件)
     */
    public Long findFptAppItemsCountByCondition(Map params, String inOutFlag) {
        String hql = "select count(ai) "
                + " from FptAppItem ai left join ai.hsUnit hsU left join ai.fptAppHead ah left join ah.scmCoc sc "
                + " where ah.company=? " + " and ah.inOutFlag = '" + inOutFlag + "' and ai.inOutFlag = '" + inOutFlag + "' ";
        List conditions = new ArrayList();
        conditions.add(CmpServerUtils.getCompany());
        if (params.get("inOutFlag") != null) {
            hql += " and ah.inOutFlag = ? ";
            conditions.add(params.get("inOutFlag"));
        }
        if (params.get("scmCoc") != null) {
            hql += " and ah.scmCoc = ? ";
            conditions.add(params.get("scmCoc"));
        }
        if (params.get("inBeginDate") != null) {
            hql += " and ah.inDeclareDate >= ? ";
            conditions.add(params.get("inBeginDate"));
        }
        if (params.get("inEndDate") != null) {
            hql += " and ah.inDeclareDate <= ? ";
            conditions.add(params.get("inEndDate"));
        }
        if (params.get("outBeginDate") != null) {
            hql += " and ah.outDeclareDate >= ? ";
            conditions.add(params.get("outBeginDate"));
        }
        if (params.get("outEndDate") != null) {
            hql += " and ah.outDeclareDate <= ? ";
            conditions.add(params.get("outEndDate"));
        }
        if (params.get("appNo") != null) {
            hql += " and ah.appNo = ? ";
            conditions.add(params.get("appNo"));
        }
        if (params.get("inEmsNo") != null) {
            hql += " and ah.inEmsNo = ? ";
            conditions.add(params.get("inEmsNo"));
        }
        if (params.get("emsNo") != null) {
            hql += " and ah.outEmsNo = ? ";
            conditions.add(params.get("emsNo"));
        }
        if (params.get("contrNo") != null) {
            hql += " and ah.contractNo = ? ";
            conditions.add(params.get("contrNo"));
        }
        hql += " group by ah.id,ai.hsNo,sc.code,sc.name,ah.appNo,ai.hsCode,ai.hsName,ai.hsModel,hsU.name,"
                + (inOutFlag.equals(FptInOutFlag.IN) ? "ah.inDeclareDate " : "ah.outDeclareDate ");
        List list = this.findList(hql, conditions.toArray());
        if (list != null) {
            return Long.parseLong(list.size() + "");
        }
        return null;
    }

1.按分组后的组数来表示记录总数，也就是分组后count返回的结果个数

2.为什么用coalesce包裹，因为子查询返回的可能是空值，在因为如果返回的是null值再java中再作运算会报错。