【SQL】列转字符串函数

还是这个需求

主界面的列表表格是直接在后台用SQL查出来的

public String getQuerySql(ElemBean condition, List<Object> params) {
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT * FROM (SELECT T.ID, T.MAINTAIN_ID, T.VIN, T.VEHICLE_MODEL_NAME, \n");
    sql.append("       T.VEHICLE_MODEL_CODE, T.RECOMMEND_ASC_AREA, T.VEHICLE_NO, \n");
    sql.append("       T.CLAIMANT_NAME, T.CTO_NAME, T.CTO_PHONE, \n"); // 2021年4月27日 11点30分 daizhizhou
    sql.append("       T.CREATED_AT, T.STATUS, T.RECOMMEND_ASC_CODE, T.RECOMMEND_ASC_NAME, \n");
    sql.append("       T.NAME, T.PHONE, T.MILEAGE, T.MANUFACTURING_DATE, T.PRODUCTION_DATE, T.FIRST_CLOSE_DATE, \n");
    sql.append("       T.FIRST_SAVE_DATE, (SELECT MAX(CREATED_AT) FROM TT_FORESEE_MAINTAIN_ORDER_AUDIT TI1 WHERE TI1.ORDER_ID=T.ID AND APPLY_STATUS=56771003) APPLY_CLOSE_DATE, \n");


    sql.append("       (SELECT MAX(CREATED_AT) FROM TT_FORESEE_MAINTAIN_ORDER_AUDIT TI1 WHERE TI1.ORDER_ID=T.ID AND APPLY_STATUS=56771006) APPLY_OTHER_DATE, \n");
    sql.append("       (SELECT APPLY_STATUS FROM TT_FORESEE_MAINTAIN_ORDER_AUDIT TI1 WHERE TI1.ORDER_ID=T.ID ORDER BY TI1.CREATED_AT DESC LIMIT 1) MAX_APPLY_STATUS, \n");
    sql.append("       (SELECT IF(APPLY_STATUS=56771003, APPLY_CLOSE_REMARK, APPLY_OTHER_ASC_REMARK) FROM TT_FORESEE_MAINTAIN_ORDER_AUDIT TI1 WHERE TI1.ORDER_ID=T.ID ORDER BY TI1.CREATED_AT DESC LIMIT 1) MAX_APPLY_REMARK, \n");
    sql.append("       (SELECT AUDIT_REMARK FROM TT_FORESEE_MAINTAIN_ORDER_AUDIT TI1 WHERE TI1.ORDER_ID=T.ID ORDER BY TI1.CREATED_AT DESC LIMIT 1) MAX_AUDIT_REMAR, \n");
    sql.append("       (SELECT GROUP_CONCAT(FAULT_DESC) FROM TT_FORESEE_MAINTAIN_ORDER_DETAIL TI1 WHERE TI1.ORDER_ID=T.ID) FAULT_DESC, \n");
    sql.append("       (SELECT GROUP_CONCAT(CORRECTIVE_ACTION) FROM TT_FORESEE_MAINTAIN_ORDER_DETAIL TI1 WHERE TI1.ORDER_ID=T.ID) CORRECTIVE_ACTION, \n");
    
    sql.append("       (SELECT GROUP_CONCAT(FAULT_CODE) FROM TT_FORESEE_MAINTAIN_ORDER_DETAIL TI1 WHERE TI1.ORDER_ID=T.ID) FAULT_CODE, \n");
    sql.append("       (SELECT GROUP_CONCAT(FAULT_LEVEL) FROM TT_FORESEE_MAINTAIN_ORDER_DETAIL TI1 WHERE TI1.ORDER_ID=T.ID) FAULT_LEVEL \n");
    sql.append("  FROM TT_FORESEE_MAINTAIN_ORDER T \n");
    sql.append("  LEFT JOIN TM_VEHICLE_BASE TV ON TV.VIN = T.VIN \n");
    sql.append("  LEFT JOIN VW_PRODUCT VP ON VP.MODEL_CODE = TV.MODEL_CODE \n");
    sql.append(" WHERE 1 = 1 \n");
    
    UserInfoDto userInfo = UserUtil.getSessionUser();
    if(userInfo.getOrgType().intValue() == Constant.ORG_TYPE_10 || userInfo.getOrgType().intValue() == Constant.ORG_TYPE_13) {
        sql.append("     AND T.RECOMMEND_ASC_CODE = ? \n");
        params.add(userInfo.getOrgCode());
    }else if(!UserUtil.isFactoryType(userInfo.getOrgType())) {
        sql.append("     AND 1<>1 \n");
    }
    if(!condition.isNull("vehicleModelCode")) {
        sql.append("     AND T.VEHICLE_MODEL_CODE LIKE ? \n");
        params.add("%"+condition.getStr("vehicleModelCode")+"%");
    }
    if(!condition.isNull("area")) {
        sql.append("     AND FIND_IN_SET(T.RECOMMEND_ASC_AREA,?)  \n");
        params.add("%"+condition.getStr("area")+"%");
    }
    if(!condition.isNull("vin")) {
        sql.append("     AND T.VIN LIKE ? \n");
        params.add("%"+condition.getStr("vin")+"%");
    }
    if(!condition.isNull("vehicleNo")) {
        sql.append("     AND T.VEHICLE_NO LIKE ? \n");
        params.add("%"+condition.getStr("vehicleNo")+"%");
    }
    if(!condition.isNull("createStardate")) {
        sql.append("     AND T.CREATED_AT >= ? \n");
        params.add(condition.getStr("createStardate"));
    }
    if(!condition.isNull("createEndate")) {
        sql.append("     AND T.CREATED_AT < DATE_ADD(?, INTERVAL 1 DAY) \n");
        params.add(condition.getStr("createEndate"));
    }
    if(!condition.isNull("maintainId")) {
        sql.append("     AND T.MAINTAIN_ID LIKE ? \n");
        params.add("%"+condition.getStr("maintainId")+"%");
    }
    if(!condition.isNull("status")) {
        sql.append("     AND T.STATUS = ? \n");
        params.add(condition.getInt("status"));
    }
    if(!condition.isNull("recommendAscCode")) {
        sql.append("     AND T.RECOMMEND_ASC_CODE LIKE ? \n");
        params.add("%"+condition.getStr("recommendAscCode")+"%");
    }
    if(!condition.isNull("recommendAscName")) {
        sql.append("     AND T.RECOMMEND_ASC_NAME LIKE ? \n");
        params.add("%"+condition.getStr("recommendAscName")+"%");
    }
    if(!condition.isNull("phone")) {
        sql.append("     AND T.PHONE LIKE ? \n");
        params.add("%"+condition.getStr("phone")+"%");
    }
    if(!condition.isNull("faultCode")) {
        sql.append("     AND T.ID IN (SELECT ORDER_ID FROM TT_FORESEE_MAINTAIN_ORDER_DETAIL WHERE FAULT_CODE LIKE ?) \n");
        params.add("%"+condition.getStr("faultCode")+"%");
    }
    if(!condition.isNull("faultLevel")) {
        sql.append("     AND T.ID IN (SELECT ORDER_ID FROM TT_FORESEE_MAINTAIN_ORDER_DETAIL WHERE FAULT_LEVEL = ?) \n");
        params.add(condition.getStr("faultLevel"));
    }
    sql.append(scSqlUtil.getCyBrandWhereSql("vp.BRAND_ID", params));
    sql.append("     ) A \n");
    return sql.toString();
}

这4个字段是从employee进行取值的，这意味着需要直接从SQL中处理好直接带给程序【后台无法处理】

 

联表 是主表的ID关联 其他表的外键 ORDER_ID：

 WHERE TI1.ORDER_ID = T.ID 

合并SQL，最后发现，下面的SQL已经这么做了

--         (SELECT group_concat(`NAME` SEPARATOR ', ') FROM TT_FORESEE_MAINTAIN_ORDER_EMPLOYEE EMP WHERE T.ID = EMP.ORDER_ID AND EMP.TYPE = 0) CLAIMANT_NAME,
--         (SELECT group_concat(`NAME` SEPARATOR ', ') FROM TT_FORESEE_MAINTAIN_ORDER_EMPLOYEE EMP WHERE T.ID = EMP.ORDER_ID AND EMP.TYPE = 0) CTO_NAME,
--         (SELECT group_concat(`PHONE` SEPARATOR ', ') FROM TT_FORESEE_MAINTAIN_ORDER_EMPLOYEE EMP WHERE T.ID = EMP.ORDER_ID AND EMP.TYPE = 0) CTO_PHONE,