多表连接分页查询和搜索功能

多表连接分页查询

现有学生表，小组表，班级表，城市表，标签表

一个学生有多个标签，分页查询时，应该先分页，再表连接

    <sql id="SELECT_SCGC">
        select s.s_id as s_id  ,  s.s_name as s_name  ,  s.s_phone as s_phone  ,  s.s_qq as s_qq  ,  s.s_age as s_age  ,  s.s_birthday as s_birthday  ,  s.s_starts as s_starts  ,  s.s_attr as s_attr,
                c.clazz_id as clazz_id  ,  c.c_name as c_name  ,
                g.g_id as g_id  ,  g.g_name as g_name  ,  g.g_createdate as g_createdate  ,
                y.c_id as y_id  ,  y.c_name as y_name  ,
                m.m_id as m_id  ,  m.m_name as m_name
    </sql>

    <select id="selectLimit" resultMap="StudentResultMap">
        <include refid="SELECT_SCGC"></include>
        from ( select * from T_STUDENT limit #{begin},#{size} )
            s left join T_CITY y
            on s.c_id = y.c_id
            left join T_GROUP g
            on s.g_id = g.g_id
            left join T_CLAZZ as c
            on s.clazz_id = c.clazz_id
            left join M_S_FK ms
            on s.s_id = ms.s_id
            left join T_MARK as m
            on ms.m_id = m.m_id
    </select>

 

搜索功能

sql：先条件查询，最后连接

    <sql id="SELECT_SCGC">
        select s.s_id as s_id  ,  s.s_name as s_name  ,  s.s_phone as s_phone  ,  s.s_qq as s_qq  ,  s.s_age as s_age  ,  s.s_birthday as s_birthday  ,  s.s_starts as s_starts  ,  s.s_attr as s_attr,
                c.clazz_id as clazz_id  ,  c.c_name as c_name  ,
                g.g_id as g_id  ,  g.g_name as g_name  ,  g.g_createdate as g_createdate  ,
                y.c_id as y_id  ,  y.c_name as y_name  ,
                m.m_id as m_id  ,  m.m_name as m_name
    </sql>
    <select id="selectByContent" resultMap="StudentResultMap">
        <include refid="SELECT_SCGC"/>
            from (select * from T_STUDENT where ${colName} like '%${colValue}%')
                s left join T_CITY y
                on s.c_id = y.c_id
                left join T_GROUP g
                on s.g_id = g.g_id
                left join T_CLAZZ as c
                on s.clazz_id = c.clazz_id
                left join M_S_FK ms
                on s.s_id = ms.s_id
                left join T_MARK as m
                on ms.m_id = m.m_id
    </select>