1.一对多中按年月分组
<resultMap id="stuClassAwayQuery" type="com.wlpy.common.vo.report.StuPrecollectedVo">
<result column="stuId" jdbcType="INTEGER" property="stuId" />
<result column="classId" jdbcType="VARCHAR" property="classId" />
// 数据过多 for查询导致效率低
<collection property="yearQueryVO" select="yearQuery" column="{stuId=id,classId=classId}">
<result column="year" jdbcType="VARCHAR" property="year" />
<result column="month" jdbcType="VARCHAR" property="month" />
<result column="money" jdbcType="VARCHAR" property="money" />
</collection>
</resultMap>
SELECT
ar.class_id,
ar.stu_id
FROM
apply_relation AS ar
SELECT
DATE_FORMAT( scr.attendance_time, '%Y-%m-%d' ) AS `year`,
DATE_FORMAT( scr.attendance_time, '%m' ) AS `month`,
IFNULL( sum( scr.course_price ), 0 ) AS money
FROM
stu_course_relation AS scr
WHERE
scr.class_id = 1 #{classId}
AND scr.stu_id = #{stuId}
GROUP BY
DATE_FORMAT(
scr.attendance_time,
'%Y%m')