sql题目挑战

先看表结构，一共5张表

问题描述

获取模拟考试的reaName，stuid，classes，title，以及对应的result,usetime,submitTime,momery,language,并按照提交时间的降序排序

参考解答

    <select id="SatusListandSearch" resultType="com.swust.power.entity.test_paper.StatusTable">
        select a.submitTime,a.result,a.momery,a.language,a.codelen,b.title,c.stuid,c.classes,d.realName from exam_score as a
        left join problem as b on a.pid=b.pid
        left join cprogram_user_info as c on a.uid=c.uid
        left join user as d on a.uid=d.uid
        left join test_paper as e on a.tid=e.id
        <where>
            a.result is not null and e.model=0
        </where>
        order by a.submitTime desc
    </select>

进阶挑战

在上个问题的基础上，要根据stuid,title,result（三个值可能为null） 进行查询呢？

    <select id="SatusListandSearch" resultType="com.swust.power.entity.test_paper.StatusTable">
        select a.submitTime,a.result,a.momery,a.language,a.codelen,b.title,c.stuid,c.classes,d.realName from exam_score as a
        left join problem as b on a.pid=b.pid
        left join cprogram_user_info as c on a.uid=c.uid
        left join user as d on a.uid=d.uid
        left join test_paper as e on a.tid=e.id
        <where>
            a.result is not null and e.model=0
            <if test="statusvo.stuid!=null">
                and  c.stuid=#{statusvo.stuid}
            </if>
            <if test="statusvo.title!=null">
                and b.title like "%${statusvo.title}%"
            </if>
            <if test="statusvo.result!=null">
                and a.result=#{statusvo.result}
            </if>
        </where>
        order by a.submitTime desc
    </select>

注释

该sql是xml中的写法，其中

resultType="com.swust.power.entity.test_paper.StatusTable"

的实体类定义如下：

@Data
public class StatusTable {
    // from cprogram_user_info
    private String stuid;
    // from cprogram_user_info
    private  String classes;
    //from user
    private String realName;

    /**
     * 消耗时间
     */

    private  String usetime;
    /**
     * 消耗内存
     */

    private  String momery;
    /**
     * 语言
     */

    private  String language;
    /**
     * 代码长度
     */

    private  String codelen;
    /**
     * 结果
     */

    private  String result;

    private String submitTime;

    //from problem
    private String title;

}

思路

理清表之间的连接关系即可

结果示例