<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="vip.xiaonuo.disk.mapper.ClassTeamMapper">
    <select id="page" resultType="vip.xiaonuo.disk.vo.classteam.ClassTeamVo">
        SELECT
            a."id",
            a."class_name" className,
            a."class_code" classCode,
            a."major_id" majorId,
            e.major_name majorName,
            a."college_id" collegeId,
            b.name collegeName,
            a."remark",
            a.DELETE_FLAG,
            a.UPDATE_TIME,
            a.CREATE_TIME,
            a.CREATE_USER,
            a.UPDATE_USER,
            a.COLLEGE_TWO_ID,
            c.name collegeTwoName,
            a.COLLEGE_THREE_ID,
            d.name collegeThreeName
        FROM
            SYSDBA."class_team" a
        LEFT JOIN college b ON   a.COLLEGE_ID = b.id
        LEFT JOIN college c ON   a.COLLEGE_TWO_ID = c.id
        LEFT JOIN college d ON   a.COLLEGE_THREE_ID = d.id
        LEFT JOIN major e   ON   a.MAJOR_ID = e.id
        <where>
              <if test="classTeamPageParam.className != null and classTeamPageParam.className != ''">
                  a."class_name" LIKE concat('%',#{classTeamPageParam.className},'%')
              </if>
        </where>
        order by a.UPDATE_TIME desc
    </select>
    <select id="detail" resultType="vip.xiaonuo.disk.vo.classteam.ClassTeamVo">
        SELECT
            a."id",
            a."class_name" className,
            a."class_code" classCode,
            a."major_id" majorId,
            e.major_name majorName,
            a."college_id" collegeId,
            b.name collegeName,
            a."remark",
            a.DELETE_FLAG,
            a.UPDATE_TIME,
            a.CREATE_TIME,
            a.CREATE_USER,
            a.UPDATE_USER,
            a.COLLEGE_TWO_ID,
            c.name collegeTwoName,
            a.COLLEGE_THREE_ID,
            d.name collegeThreeName
        FROM
            SYSDBA."class_team" a
        LEFT JOIN college b ON   a.COLLEGE_ID = b.id
        LEFT JOIN college c ON   a.COLLEGE_TWO_ID = c.id
        LEFT JOIN college d ON   a.COLLEGE_THREE_ID = d.id
        LEFT JOIN major e   ON   a.MAJOR_ID = e.id
        where a.id=#{id}
    </select>
</mapper>
