SQL 建立临时表进行查询

SELECT
    min (maxrank) as minofmaxrank
FROM
    (SELECT
        max(rank) as maxrank
    FROM
        tbl_core_staff
    WHERE
        st_ymd<=now() AND
        ed_ymd>now() AND
        staff_cd in ('','','','','','','','','','','','','','','','','','','')
    GROUP BY
        staff_cd
    ) t1

 这里是先从tbl_core_staff 表查询分组的最大值的rank 然后查询这个最大值rank组的最小值

用其他表和子表进行联合查询：

    SELECT
        *
         FROM  
            bbb INNER JOIN 
            ( SELECT  
                a1,
                inner_cd,
               MAX( case when .aaa.SLIDE_SEQ = 1 THEN SLIDE_QTY  END  )as SLIDE_QTY1,
                MAX( case when .aaa.SLIDE_SEQ = 2 THEN SLIDE_QTY  END  )as SLIDE_QTY2,
                MAX( case when .aaa.SLIDE_SEQ = 3 THEN SLIDE_QTY  END  )as SLIDE_QTY3,
                MAX( case when .aaa.SLIDE_SEQ = 4 THEN SLIDE_QTY  END  )as SLIDE_QTY4
            FROM
                aaa
            WHERE 
                aaa.a1 = 'ADDM01-20160612-1-100002-001'   AND 
                aaa.subsidiary_cd = 'MJP' AND 
                aaa.sub_a1 = '1'
            GROUP BY 
                aaa.inner_cd,
                aaa.a1
            )  tmp1 on  bbb.inner_cd = tmp1.inner_cd
            
        WHERE 
                bbb.a1 = #{ankenId,jdbcType=VARCHAR}  AND
                bbb.subsidiary_cd = #{subsidiaryCd,jdbcType=VARCHAR} AND
                bbb.sub_a1 = #{subAnkenId,jdbcType=VARCHAR} AND
    ORDER BY
                bbb.subsidiary_cd ASC,
                bbb.brand_cd ASC,
                bbb.product_cd ASC