七个表的复杂查询

 public List findDetailSumById(Long id) throws Exception {
        List sumList=new ArrayList();
        StringBuffer sb = new StringBuffer(" ");
        sb.append("select sup.materialcode as materialcode,sup.materialname as materialname,sup.is_auto_send_erp,");
        sb.append("sup.providerproductname  as providerproductname , sup.measurename as measurename,");
        sb.append("sum(sup.amount) as amount,sum(sup.money) as money,");
        sb.append("sup.providername as providername, sup.locationname as locationname,");
        sb.append("sup.projectcode as  projectcode,sup.materialproperty as materialproperty,");
        sb.append("sup.providerproductcode as providerproductcode, sup.submeasurename as submeasurename,");
        sb.append("sup.providershortname as providershortname ,");
        sb.append("sup.ismainproduct as ismainproduct,sup.inlocationname as inlocationname");
        sb.append(" from");
        sb.append("(");
        sb.append("select spm.entity_code as  materialcode,spm.entity_name as  materialname,sibd.is_auto_send_erp ,");
        sb.append("sibd.provider_product_name as providerproductname,sibd.is_main_product as ismainproduct,");
        sb.append(" nvl( sibd.measure_name,' ')  as  measurename,nvl( sibd.sub_measure_name,' ') as submeasurename,");
        sb.append("nvl(sibd.amount,0) as amount,nvl(sibd.money,0) as money,");
        sb.append(" spr.entity_name as providername,spr.short_name as providershortname ,nvl(swl.entity_name,' ') as locationname ,");
        sb.append(" nvl( sibd.entity_desc,' ') as entitydesc,nvl( sp.entity_code,' ') as projectcode,");
        sb.append(" nvl( sibd.provider_product_code,' ') as providerproductcode,nvl( smp.entity_name,' ') as materialproperty,");
        sb.append(" sibd.output_bill_id as outputbillid,nvl(swlin.entity_name,' ') as inlocationname");
        sb.append(" from scm_output_bill_detail sibd");
        sb.append(" left outer join scm_product_material spm on spm.id =sibd.product_material_id");
        sb.append(" left outer join scm_provider spr on spr.id = sibd.provider_id");
        sb.append(" left outer join scm_warehouse_location swl on swl.id =sibd.warehouse_location_id");
        sb.append(" left outer join scm_warehouse_location swlin on swlin.id =sibd.in_warehouse_location_id");
        sb.append(" left outer join scm_project sp on sp.id = sibd.project_id");
        sb.append(" left outer join scm_material_property smp on smp.id=sibd.material_property_id");
        sb.append(") sup"); 
        sb.append(" where sup.outputbillid=?");
        sb.append(" group by");
        sb.append(" sup.materialcode,sup.materialname,sup.materialproperty, sup.measurename,sup.projectcode,");
        sb.append("sup.providername,sup.providerproductname,sup.providerproductcode,sup.locationname,sup.is_auto_send_erp ,");
        sb.append("sup.providershortname,sup.submeasurename,sup.ismainproduct,sup.inlocationname");
        sb.append(" order by");
        sb.append(" sup.materialcode,sup.materialname,sup.materialproperty,sup.measurename,sup.projectcode,");
        sb.append("sup.providername,sup.providerproductname,sup.providerproductcode,sup.locationname,");
        sb.append("sup.submeasurename,sup.providershortname");
        
        sumList = getJdbcTemplate().query(sb.toString(), new Object[]{id}, new RowMapper(){
            public Object mapRow(ResultSet rt, int arg1) throws SQLException {
                OutputDetailSumBean outDetailSumBean=new OutputDetailSumBean();
                outDetailSumBean.setMaterialCode(rt.getString("materialcode"));
                outDetailSumBean.setMaterialName(rt.getString("materialname"));
                outDetailSumBean.setProjectCode(rt.getString("projectcode")); 
                outDetailSumBean.setProviderProductName(rt.getString("providerproductname"));
                outDetailSumBean.setMaterialProperty(rt.getString("materialproperty"));
                outDetailSumBean.setSubmeasureName(rt.getString("submeasurename"));
                outDetailSumBean.setProviderProductCode(rt.getString("providerproductcode"));
                outDetailSumBean.setProviderShortName(rt.getString("providershortname"));
                outDetailSumBean.setProviderName(rt.getString("providername"));
                outDetailSumBean.setLocationName(rt.getString("locationname"));
                outDetailSumBean.setInlocationName(rt.getString("inlocationname"));
                outDetailSumBean.setMeasureName(rt.getString("measurename"));
                outDetailSumBean.setIsmainProduct(rt.getLong("ismainproduct"));
                outDetailSumBean.setAmount(rt.getDouble("amount"));
              //  outDetailSumBean.setPrice(rt.getDouble("price"));
                outDetailSumBean.setMoney(rt.getDouble("money")); 
                outDetailSumBean.setIsAutoSendERP(rt.getLong("is_auto_send_erp"));
                
                return outDetailSumBean;
            }
        });
         return sumList;
    }