7个表的复杂查询

     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;

    }
  

7个表的复杂查询