在使用mybatis中指定字段查询

1：需求：查询学过“叶平”老师所教的所有课的同学的学号、姓名；

List<Map<String,Object>> selectYepingAllCourse(@Param("name") String name);

用

List<Map<String,Object>>来接收从数据库查询的数据

<!--学叶平老师教的所有课的学生的学号，姓名-->
<select id="selectYepingAllCourse" resultMap="Base_ResultMap">
            SELECT
            a.s_id,
            a.sname
        FROM
            a_student a
        WHERE
            a.s_id IN (
                SELECT
            a.s_id
        FROM
            a_sc a
        INNER JOIN a_course b ON a.c_id = b.c_id
        INNER JOIN a_teacher c ON c.t_id = b.t_id
        WHERE
            c.tname = #{name}
        GROUP BY
            a.s_id
        HAVING
            count(a.c_id) = (
                SELECT
                    count(a_course.c_id)
                FROM
                    a_course
                INNER JOIN a_teacher ON a_teacher.t_id = a_course.t_id
                WHERE
                    a_teacher.tname = #{name}
            )
            );
</select>

分析一下这个sql。

1：都有哪些学生学习了叶平老师的课。
    SELECT
    a.s_id,count(a.c_id)
    FROM
    a_sc a
    INNER JOIN a_course b ON a.c_id = b.c_id
    INNER JOIN a_teacher c ON c.t_id = b.t_id
            WHERE
    c.tname = '叶平'
    GROUP BY
    a.s_id
    2：叶平老师教了几门课。
    SELECT
            count(a_course.c_id)
        FROM
            a_course
        INNER JOIN a_teacher ON a_teacher.t_id = a_course.t_id
        WHERE
            a_teacher.tname = '叶平'
    )
    3：学叶平老师课和叶平老师教的课一样的学生的信息。
    SELECT
    a.s_id
FROM
    a_sc a
INNER JOIN a_course b ON a.c_id = b.c_id
INNER JOIN a_teacher c ON c.t_id = b.t_id
WHERE
    c.tname = '叶平'
GROUP BY
    a.s_id
HAVING
    count(a.c_id) = (
        SELECT
            count(a_course.c_id)
        FROM
            a_course
        INNER JOIN a_teacher ON a_teacher.t_id = a_course.t_id
        WHERE
            a_teacher.tname = '叶平'
    )

测试：

/**
     * 查询学过“叶平”老师所教的所有课的同学的学号、姓名；
     */
    @Test
    public void testSelectYepingAllCourse(){
        List<Map<String, Object>> list = ascMapper.selectYepingAllCourse("李一");
        for (Map<String,Object> i:list){
            System.out.println(i);
        }
    }

输出的结果：

得到List集合中Map集合的值。

for(int i=0;i<list.size();i++){
            Map map=list.get(i);
            //是用set来接收的。不能重复。无顺序
            Set set = map.keySet();
            System.out.println("set的值 "+set);
            Object sId = map.get("sId");
            System.out.println("得到sId的值 "+sId);
        }

输出结果：这个只有一条结果。

下面看下多个结果的。