id_, device_id_, contract_id_ select * from( SELECT d.id_, d.code_, d.name_, d.ip_, d.lgtd_, d.lttd_, d.address_,d.status_ , t.name_ as proTypeName,t.code_ as proTypeCode,rg.contract_id_ FROM device_info d LEFT JOIN device_relation_contract rg on rg.device_id_ = d.id_ and rg.contract_id_ = #{vo.contractId} LEFT JOIN device_product_info p on p.id_ = d.product_id_ LEFT JOIN device_product_type t on t.id_ = p.type_id_ WHERE d.is_dele_ = '0' and p.is_dele_ = '0' and t.is_dele_ = '0' union all SELECT d.id_, d.code_, d.name_, d.ip_, d.lgtd_,d.lttd_, d.address_, d.status_, '摄像头' as deviceType,'video' as deviceTypeCode,rg.contract_id_ FROM device_video_point d LEFT JOIN device_relation_contract rg on rg.device_id_ = d.id_ and rg.contract_id_ = #{vo.contractId} ) tab and tab.proTypeCode =#{vo.proTypeCode} and tab.code_ = #{vo.deviceCode} and tab.name_ like concat('%',#{vo.deviceName},'%') SELECT rc.*, d.name_, d.code_, d.product_id_, d.full_name_ AS fullName, u.NAME_ AS managerUserName, c.COMPANY_NAME_ AS supplierCompanyName, t.name_ AS typeName, p.name_ AS productName FROM device_relation_contract rc LEFT JOIN device_info d ON rc.device_id_ = d.id_ LEFT JOIN device_base_company c ON c.id_ = d.supplier_company_id_ LEFT JOIN device_base_user u ON u.id_ = d.manager_user_id_ LEFT JOIN device_product_info p ON p.id_ = d.product_id_ LEFT JOIN device_product_type t ON t.id_ = p.type_id_ ${ew.customSqlSegment} WHERE AND d.IS_DELE_ = 0 SELECT rc.id_,c.COMPANY_NAME_ AS companyName, d.* FROM device_relation_contract rc LEFT JOIN device_base_contract d ON rc.contract_id_ = d.id_ LEFT JOIN device_relation_supplier_contract rsc ON rsc.CONTRACT_ID_ = d.ID_ LEFT JOIN device_base_company c ON c.id_ = rsc.COMPANY_ID_ ${ew.customSqlSegment} WHERE AND d.IS_DELE_ = 0