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