当前位置：首页 > news >正文

在使用mybatis中指定字段查询

news 来源：原创 2024/5/13 3:30:11

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

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

用

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

 1 <!--学叶平老师教的所有课的学生的学号，姓名-->
 2 <select id="selectYepingAllCourse" resultMap="Base_ResultMap">
 3             SELECT
 4             a.s_id,
 5             a.sname
 6         FROM
 7             a_student a
 8         WHERE
 9             a.s_id IN (
10                 SELECT
11             a.s_id
12         FROM
13             a_sc a
14         INNER JOIN a_course b ON a.c_id = b.c_id
15         INNER JOIN a_teacher c ON c.t_id = b.t_id
16         WHERE
17             c.tname = #{name}
18         GROUP BY
19             a.s_id
20         HAVING
21             count(a.c_id) = (
22                 SELECT
23                     count(a_course.c_id)
24                 FROM
25                     a_course
26                 INNER JOIN a_teacher ON a_teacher.t_id = a_course.t_id
27                 WHERE
28                     a_teacher.tname = #{name}
29             )
30             );
31 </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集合的值。

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