查询测试用例管理表数据并判断关联文件信息是否存在

SELECT DISTINCT caseAss.* FROM ( SELECT a.SCENA_CODE AS "scenaCode", a.CASE_CODE AS "caseCode", a.ROAD_CODE AS "linkRoads", a.CASE_DESCRIBE AS "caseDescribe", a.CASE_TYPE AS "caseType", ltctn.TYPE_NAME AS "caseTypeName", a.TEST_OBJECTIVE AS "testObjective", a.TEST_CONDITION AS "testCondition", a.CREATE_USER AS "createUser", a.UPDATE_USER AS "updateUser", a.remark AS "remark", a.id AS "id", a.create_by AS "createBy", a.create_dept AS "createDept", a.create_time AS "createTime", a.update_by AS "updateBy", a.update_time AS "updateTime", a.update_ip AS "updateIp", a.version AS "version", a.del_flag AS "delFlag", lsm.SCENA_CODE AS "name", ltctn.PARENT_NAMES AS "caseTypeNames", ( SELECT COUNT() FROM lts_file_info WHERE LINK_CODE = a.id ) AS "attachmentCount" FROM lts_test_case_manage a LEFT JOIN lts_test_case_type_new ltctn ON a.CASE_TYPE = ltctn.id LEFT JOIN lts_scena_manage lsm ON a.SCENA_CODE = lsm.id LEFT JOIN lts_scena_type_new lsmt ON lsm.SCENA_TYPE = lsmt.id LEFT JOIN sys_user t ON FIND_IN_SET(t.id, a.create_by) LEFT JOIN sys_dept d ON t.dept_id = d.id WHERE a.del_flag = #{DEL_FLAG_NORMAL} AND lsm.SCENA_TYPE IN ( SELECT id FROM lts_scena_type_new WHERE id = #{scenaType} OR find_in_set(#{scenaType}, REPLACE(PARENT_IDS, '/', ',')) ) AND lsm.SCENA_CODE LIKE concat('%', #{scenaCode}, '%') AND a.CASE_CODE LIKE concat('%', #{caseCode}, '%') AND a.CASE_DESCRIBE LIKE concat('%', #{caseDescribe}, '%') AND d.id = #{assUser} AND a.CREATE_USER = #{createUser} AND a.CASE_TYPE IN ( SELECT id FROM lts_test_case_type_new t WHERE id = #{caseType} OR find_in_set(#{caseType}, REPLACE(PARENT_IDS, '/', ',')) ) AND ( SELECT COUNT() FROM lts_file_info WHERE LINK_CODE = a.id ) > 0 AND a.ROAD_CODE IS NULL ${params.dataScope} ) caseAss ORDER BY caseAss.caseCode ASC