SELECT    min (maxrank) as minofmaxrankFROM    (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