sql拼接加截取sql里面的年月

  /**
      * 
      * 获取保理的金额,开票金额，税额，抵扣价税合计
      */
     @Rest(method = RestMethod.POST)
     @ActionKey("/api/qhyf/abs/BaoLi/getBaoLiMoney")
     public void getBaoLiMoney() {
         //返回json数据
         JSONObject returnJson = new JSONObject();
         JSONArray data = new JSONArray();
         
        //财务科目key值
         String financialSubjectsKey = this.getPara("financialSubjectsKey");
         try {
             if(!fsKey(financialSubjectsKey)){
                returnJson.put("errcode","2");
                returnJson.put("errmsg","key值验证不通过");
                // 返回错误结果
                this.renderJson(returnJson);
            }else{
                returnJson.put("errcode","0");
                returnJson.put("errmsg","");
                
                List<Object> paras = new ArrayList<Object>();
                String where = "";
                // 核心企业ID
                String coreEnterpriseId = this.getPara("coreEnterpriseId");
                if (StringUtils.notBlank(coreEnterpriseId)) {
                    if(StringUtils.isBlank(where)){
                        where = " Where x.core_enterprise_id = ? ";
                    }else{
                        where += " and x.core_enterprise_id = ? ";
                    }
                    paras.add(coreEnterpriseId);
                }
                // 年份
                String years = this.getPara("years");
                if (StringUtils.notBlank(years)) {
                    if(StringUtils.isBlank(where)){
                        where = " Where x.years  = ? ";
                    }else{
                        where += " and x.years  = ? ";
                    }
                    paras.add(years);
                }
                // 月份
                String months = this.getPara("months");
                if (StringUtils.notBlank(months)) {
                    if(StringUtils.isBlank(where)){
                        where = " Where x.months  = ? ";
                    }else{
                        where += " and x.months  = ? ";
                    }
                    paras.add(months);
                }
                
                String select =""+ 
                        "SELECT  " +
                        "    x.core_enterprise_id,  " +
                        "    x.years,  " +
                        "    x.months,  " +
                        "    SUM( x.invoice_amount ) as  invoice_amount ,  " +
                        "    SUM( x.invoice_tax_amount )  as invoice_tax_amount  ,  " +
                        "    SUM( x.total_amount ) as total_amount   " +
                        "FROM  " +
                        "    (  " +
                        "    SELECT  " +
                        "        a.core_enterprise_id,  " +
                        "        a.account_receivable,  " +
                        "        b.pay_id,  " +
                        "        b.invoice_amount,  " +
                        "        b.invoice_tax_amount,  " +
                        "        b.total_amount,  " +
                        "        b.invoice_date,  " +
                        "        substring( DATE_FORMAT( b.invoice_date, '%Y%m' ), 5 ) months,  " +
                        "        DATE_FORMAT( b.invoice_date, '%Y' ) years   " +
                        "    FROM  " +
                        "        biz_pay_billing_info b,  " +
                        "        biz_pay_comfirm_info a   " +
                        "    WHERE  " +
                        "        b.sys_status = 1   " +
                        "        AND b.pay_id = a.uuid   " +
                        "        AND a.sys_status = 1   " +
                        "    ) x   " +
                        where +
                        "GROUP BY  " +
                        "    x.core_enterprise_id,  " +
                        "    x.months,  " +
                        "    x.years  " ;
                 List<Record> aList = Db.find(select, paras);
                 
                 if (aList != null ) {
                     //遍历上面的结果集
                    for (Record record : aList) {
                        //获取年
                        String yers = record.getStr("years");
                        //获取月
                        String mons = record.getStr("months");
                        //获取核心企业
                        String coreEnterId = record.getStr("coreEnterpriseId");
                        
                        String getSelectSql="SELECT  " +
                                "    SUM(z.account_receivable) AS  account_receivable   " +
                                "FROM  " +
                                "    (  " +
                                "        SELECT DISTINCT  " +
                                "            y.uuid,  " +
                                "            y.account_receivable  " +
                                "        FROM  " +
                                "            (  " +
                                "                SELECT  " +
                                "                    b.pay_id,  " +
                                "                    b.invoice_date,  " +
                                "                    substring(  " +
                                "                        DATE_FORMAT(b.invoice_date, '%Y%m'),  " +
                                "                        5  " +
                                "                    ) months,  " +
                                "                    DATE_FORMAT(b.invoice_date, '%Y') years  " +
                                "                FROM  " +
                                "                    biz_pay_billing_info b  " +
                                "                WHERE  " +
                                "                    b.sys_status = 1  " +
                                "            ) x,  " +
                                "            biz_pay_comfirm_info y  " +
                                "        WHERE  " +
                                "            x.pay_id = y.uuid  " +
                                "        AND y.sys_status = 1  " +
                                "        AND y.core_enterprise_id = ?  " +
                                "        AND x.years = ?  " +
                                "        AND x.months = ?  " +
                                "        ORDER BY  " +
                                "            y.pay_id  " +
                                "    ) z" ;
                        Record retc = Db.findFirst(getSelectSql,coreEnterId,yers,mons);
                        record.set("accountReceivable", retc.get("accountReceivable"));
                    }
                    //遍历返回的结果集，放入json 里面
                    for (Record record2 : aList) {
                        JSONObject json = new JSONObject();
                        //保理金额
                        json.put("accountReceivable", record2.get("accountReceivable"));
                        //开票金额invoice_amount
                        json.put("invoiceAmount", record2.get("invoiceAmount"));
                        //税额invoice_tax_amount
                        json.put("invoiceTaxAmount", record2.get("invoiceTaxAmount"));
                        //抵扣价税合计total_amount
                        json.put("totalAmount", record2.get("totalAmount"));
                        data.add(json);
                    }
                }
                 returnJson.put("data", data);
                    //返回正确结果
                this.renderJson(returnJson);
            }
          } catch (Exception e) {
             logger.error(AlertUtils.getErrMsgWithCode("CC004E003"),e);
             returnJson.put("errcode","4");
             returnJson.put("errmsg",AlertUtils.getErrMsgWithCode("CC004E003"));
             returnJson.put("data", data);
             //返回错误结果
             this.renderJson(returnJson);
         }
     }