涉及数组(Array)嵌套查询

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.aqin.dao.mapper1.TrmmIndexMapper">
    <resultMap id="BaseResultMap" type="com.aqin.dao.domain.TrmmIndexDO">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="created_time" property="createdTime" jdbcType="BIGINT"/>
        <result column="modified_time" property="modifiedTime" jdbcType="BIGINT"/>
        <result column="month" property="month" jdbcType="SMALLINT"/>
        <result column="x" property="x" jdbcType="DOUBLE"/>
        <result column="y" property="y" jdbcType="DOUBLE"/>
        <result column="my_row" property="myRow" jdbcType="INTEGER"/>
        <result column="my_col" property="myCol" jdbcType="INTEGER"/>
        <result column="trmm_min" property="trmmMin" jdbcType="DOUBLE"/>
        <result column="trmm_max" property="trmmMax" jdbcType="DOUBLE"/>
        <result column="month_arr" property="monthArr" jdbcType="ARRAY" typeHandler="com.aqin.util.ArrayTypeHandler"/>
    </resultMap>
    <resultMap id="BaseResultMap2" type="com.aqin.dao.domain.CitrusDO">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="city_id" property="cityId" jdbcType="VARCHAR"/>
        <result column="month" property="month" jdbcType="SMALLINT"/>
        <result column="test" property="test" jdbcType="SMALLINT"/>
        <result column="trmmmin" property="trmmMin" jdbcType="VARCHAR"/>
        <result column="trmmmax" property="trmmMax" jdbcType="VARCHAR"/>
        <result column="lstmin" property="lstMin" jdbcType="VARCHAR"/>
        <result column="lstmax" property="lstMax" jdbcType="VARCHAR"/>
    </resultMap>

    <select id="getTest" parameterType="java.lang.Integer" resultMap="BaseResultMap2">
        select * from trmm_index index
        left join citrus c on c.month=ANY(index.month_arr)
        WHERE c.test=#{param}
    </select>

</mapper>

这里涉及了两张表的嵌套查询（表1：trmm_index ，表2： citrus）

select * from trmm_index index

left join citrus c on c.month=ANY(index.month_arr)

WHERE c.test=#{param}

1. 先以左边的表（index）为准生成一个临时表（右表无对应的补null），条件 c.month=ANY(index.month_arr)
2. ANY()的作用相当于IN()（ANY()只适用于array，IN()也可用于list）

   

3. 根据 WHERE c.test=#{param} 对临时表进行筛选
   1. 传参（#{param}）为1时，当输出对象为左表对象类时输出红色行，当输出对象为右表对象类时输出蓝色行