表数据如上。现在要求查询的列表为按outline_group_id分组,分组后组内按照sort_no排序
错误的做法:
这样只能得到分组的第一条记录
<select id="selectByOutlineGroupIdsAndStudentId" resultMap="ResultMapWithBLOBs">
select
<include refid="Base_Column_List" />
,
<include refid="Blob_Column_List" />
from homework_student_answer
where outline_group_id in
<foreach collection="list" separator="," item="item" index="index" close=")" open="(">
#{item,jdbcType=INTEGER}
</foreach>
and student_user_id = #{studentId,jdbcType=BIGINT}
group by outline_group_id order by sort_no
</select>
正确的做法:
<select id="selectByOutlineGroupIdsAndStudentId" resultMap="ResultMapWithBLOBs">
select
<include refid="Base_Column_List" />
,
<include refid="Blob_Column_List" />
from homework_student_answer
where outline_group_id in
<foreach collection="list" separator="," item="item" index="index" close=")" open="(">
#{item,jdbcType=INTEGER}
</foreach>
and student_user_id = #{studentId,jdbcType=BIGINT}
order by outline_group_id,sort_no
</select>
即:
SELECT
*
FROM
homework_student_answer
WHERE
outline_group_id IN (
336,
337,
338,
339,
340,
341,
342,
343,
348