mybatis result 多表关联查询

墨蓝 2022-05-23 23:35 278阅读 0赞

<sql id="pZoneCoordinateAllField">coordinateAreaId,coordinateNumber,coordinateId,zoneId,coordinateAreaName,coordinateLongitude,coordinateAltitude,longitudeDegree,longitudeMinute,longitudeSecond,altitudeDegree,altitudeMinute,altitudeSecond</sql>  
    	<resultMap type="ProtectionZone" id="pzMap">  
          
        <collection property="coordinates" javaType="ArrayList"   
         column="{zoneId = zoneId}" ofType="ProtectionZoneCoordinate" select="findProtectionZoneCoordinate"/>  
        </resultMap>  
    	<select id="findProtectionZoneCoordinate" resultType="ProtectionZoneCoordinate">  
        select <include refid="pZoneCoordinateAllField" /> from protectionZoneCoordinate  
         where zoneId=#{zoneId} ;   
        </select>
    	
        <select id="selectId" resultMap="pzMap">
    		select * from (
    		select a.zonePeriodStartMonth,
    		a.zonePeriodEndMonth,
    		a.zoneStatus,
    		a.zoneOrgStaffCount,
    		a.zoneOrgTechStaffCount,
    		(select fullName from sys_user where userId=a.zoneCreator) as createName,
    		a.zoneOrgLawEnforcerCount,
    		a.zoneArea,
    		a.zoneOrgContactEmail,
    		a.zoneOrgFundOrgin,
    		a.zoneOrgWorkerCount,
    		a.zoneCoreArea,
    		a.zoneExperimentalArea,
    		a.zoneOrgFixedAnnualFund,
    		a.zoneOrgTotalAsset,
    		a.zoneId,
    		a.zoneSeries,
    		a.zoneLevel,
    		a.zoneCreator,
    		a.zoneCreateTime,
    		a.zoneEditTime,
    		a.zoneOrgLevel,
    		a.zoneOrgType,
    		a.zoneNumber,
    		a.zoneName,
    		a.zoneAreaCode,
    		a.zoneAdditionalSpecies,
    		a.zoneSurveillanceId,
    		a.zoneOrgName,
    		a.zoneOrgContactMobile,
    		a.zoneOrgLegalPerson,
    		a.zoneOrgContactName,
    		a.zoneOrgRelation,
    		a.zoneOrgNameLevel,
    		a.zoneOrgAreaOwnership,
    		a.zoneOrgContactAddress,
    		a.zoneOrgContactZip,
    		a.zoneOrgContactPhone,
    		a.isEnable
    		from protectionZone a
    		) as j
    		where zoneId=#{zoneId}
    	</select>