1为数据库查询的数据中加一排序号，2判断为查询出来的字段赋值

最新推荐文章于 2022-12-13 23:42:30 发布

BestZhao

最新推荐文章于 2022-12-13 23:42:30 发布

阅读量1k

点赞数

分类专栏： java学习记录文章标签： select 数据数据库

本文链接：https://blog.csdn.net/qq_37910658/article/details/78464826

版权

java学习记录专栏收录该内容

14 篇文章 0 订阅

订阅专栏

 @Override
    public String getSqlByFileCodeForExport(String strTime, String strCompany) {
        StringBuffer strSb = new StringBuffer();
        //加序号代码
        strSb.append("select a.*,concat((@i :=@i + 1),'','') AS CON from ");
        strSb.append("((SELECT  results.ORDER_NO AS ORDERNO, ");
        strSb.append(" results.POLICY_NO AS POLICYNO,");
        strSb.append(" results.TRADE_AMT AS TRADEAMT, ");
        strSb.append(" results.CAEJ_ORDER_NO AS CAEJORDERNO, ");
        strSb.append(" results.CAEJ_POLICY_NO AS CAEJPOLICYNO, ");
        strSb.append(" results.TRADE_SN AS TRADESN, ");
        strSb.append(" results.ORDER_AMT AS ORDERAMT, ");
        strSb.append(" results.ORDER_BUY_NUM AS ORDERBUYNUM, ");
        strSb.append(" case when results.CHK_RESULT_TYPE  = '01' then '成功' ");
        strSb.append(" else '失败' end as CHKRESULTTYPE, ");
        strSb.append(" DATE_FORMAT(results.RECONCIL_TIME,'%Y-%m-%d %H:%i:%S') as RECONCILTIME ");
        strSb.append(" FROM R_DATA_RESULT results ");
        strSb.append(" where  1=1 AND results.BUSINESS_INTERFACE='01'   ");
        if (StringUtils.isNotEmpty(strTime)) {
            // strTime = strTime.substring(0, strTime.lastIndexOf(","));
            strSb.append(" and results.RECONCIL_TIME like '%" + strTime + "%' ");
        }
        if (StringUtils.isNotEmpty(strCompany)) {
            strSb.append(" and results.SETTLE_MODE in (" + strCompany + ")");
        }
        //加序号代码
        strSb.append(") a ,(select  @i:=0)   as   it) ");
        return strSb.toString();
    }

通过数据库查询的条件判断，直接给字段赋值

 // 导出数据所用sql rdatatotal
    @Override
    public String getSqlByExport(String ids) {
        StringBuffer strSb = new StringBuffer();
        if (StringUtil.isNotEmpty(ids)) {
            ids = ids.substring(0, ids.lastIndexOf(","));
            strSb.append(
                " select a.RFILERECORDID,a.SETTLEMODE,a.TYPECODE,a.TOTALCOUNT,a.TOTALAMT,a.TOTALTYPE,"
                         + "a.BUSINESSINTERFACE,a.ACCOUNTDAY ,a.RECONCILTIME,a.SETTLE_MODE, concat((@i :=@i + 1),'','') AS CON from ");
            strSb.append(
                "( ( select recon.R_FILE_RECORDID as RFILERECORDID,recon.SETTLE_MODE as  SETTLE_MODE,");
            strSb.append(
                " case when recon.SETTLE_MODE  = 'HKBX' then '弘康' when recon.SETTLE_MODE  = 'YGBX' then '阳光' else ''  end as SETTLEMODE,");
            strSb.append(
                " case when recon.TYPE_CODE  = '1' then '追加' else '赎回' end as TYPECODE, ");
            strSb.append(" recon.TOTAL_COUNT as TOTALCOUNT, recon.TOTAL_AMT as TOTALAMT, ");
            strSb.append(
                " case when recon.TOTAL_TYPE  = '01' then '成功' else '失败' end as TOTALTYPE ,");
            strSb.append(
                " case when recon.BUSINESS_INTERFACE  = '01' then '新契约对账接口' else '保全对账接口' end  as BUSINESSINTERFACE, ");
            strSb.append(" recon.ACCOUNT_DAY as ACCOUNTDAY, ");
            strSb.append(" DATE_FORMAT(recon.RECONCIL_TIME,'%Y-%m-%d %H:%i:%S') as RECONCILTIME ");
            strSb.append(" from R_DATA_TOTAL recon");
            strSb.append(" where 1=1 AND recon.BUSINESS_INTERFACE='01' AND recon.R_TOTAL_ID in ("
                         + ids + ") ");
            strSb.append(" order by recon.R_FILE_RECORDID desc ) a ,(select  @i:=0)   as   it) ");
            return strSb.toString();
        } else {
            return "";
        }
    }