<!--2018-1-9 11:53 zxk 根据宿舍号码判断判断是否请假 / 2018-1-11 10:49 第二次修改,还需要添加一个条件楼号-->
<select id="findListApplyZIf" parameterClass="HashMap" resultClass="com.dwz.entity.DormZ">
<![CDATA[
SELECT
*
FROM
student st
LEFT JOIN
(
SELECT
a.xh AS axh,a.gotime AS gotime,a.backtime AS backtime
FROM
apply a
inner join (SELECT xh,max(gotime) AS gy FROM apply GROUP BY xh) y ON a.xh=y.xh AND a.gotime=y.gy
) ay
ON st.xh = ay.axh
LEFT JOIN roll_call rc ON st.id = rc.stuid
WHERE 1=1
]]>
<!--2018-1-11 10:49 第二次修改,还需要添加一个条件楼号-->
<isNotEmpty property="bulidingnumZ">
AND st.bulidingnum = #bulidingnumZ#
</isNotEmpty>
<isNotEmpty property="dormnum">
AND st.dormnum = #dormnum#
</isNotEmpty>
<isNotEmpty property="xm">
AND st.xm = #xm#
</isNotEmpty>
<isNotEmpty property="sid">
AND st.sid = #sid#
</isNotEmpty>
</select>
applay表中：这里面可以有多个学生的申请，每个学生可以有多个申请，时间都不同，我需要得到这个学生中gotime最大的这条数据
1.
得到所有xh的最大gotime(去重)
SELECT
xh,max(gotime) AS gy
FROM
apply
GROUP BY xh
因为我们还需要得到a.backtime，所以要用inner join内连接得到
SELECT
a.xh AS axh,a.gotime AS gotime,a.backtime AS backtime
FROM
apply a
inner join (
SELECT
xh,max(gotime) AS gy
FROM
apply
GROUP BY xh
) y
ON a.xh=y.xh AND a.gotime=y.gy
2.
得到某个xh最大的时间gotime：
SELECT
xh,max(gotime) AS gy
FROM
apply
当需要得到所有xh的最大时间gotime时（去重）：
SELECT
a.xh AS axh,a.gotime AS gotime,a.backtime AS backtime
FROM apply AS a
WHERE gotime = (
SELECT
max(b.gotime)
FROM apply AS b
WHERE a.xh = b.xh
)
