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

1 List

用

List

1 
      2  3             SELECT 4             a.s_id, 5             a.sname 6         FROM 7             a_student a 8         WHERE 9             a.s_id IN (10                 SELECT11             a.s_id12         FROM13             a_sc a14         INNER JOIN a_course b ON a.c_id = b.c_id15         INNER JOIN a_teacher c ON c.t_id = b.t_id16         WHERE17             c.tname = #{name}18         GROUP BY19             a.s_id20         HAVING21             count(a.c_id) = (22                 SELECT23                     count(a_course.c_id)24                 FROM25                     a_course26                 INNER JOIN a_teacher ON a_teacher.t_id = a_course.t_id27                 WHERE28                     a_teacher.tname = #{name}29             )30             );31 

分析一下这个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_idFROM    a_sc aINNER JOIN a_course b ON a.c_id = b.c_idINNER JOIN a_teacher c ON c.t_id = b.t_idWHERE    c.tname = '叶平'GROUP BY    a.s_idHAVING    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

输出的结果：

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

1 for(int i=0;i

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

下面看下多个结果的。