mysql - 两表关联查询，按条件筛选结果

Question

有如下两张表

tb_test_paper

字段：test_time , applicant_id

tb_applicant

字段id , id_card(不唯一) , check_status , data_3

其中上表applicant_id与下表id关联

问题:两表关联查询，idCard为传入参数，查询出满足下面三种条件的信息，sql该怎么写？在mybatis中怎么写？

1.id_card=idCard check_status=0

2.id_card=idCard check_status=1 data_3=0

3.id_card=idCard check_status=1 data_3=1 test_time>系统当前时间

Answer 1

<select parameterType="map">
    select * from tb_test_paper tp, tb_applicant ta
    <where>
        tp.applicant_id = ta.id
        <if test="check_status != null and check_status !='' ">
            and ta.check_status = #{check_status }
        </if>
        <if test="data_3 != null and data_3 !=''">
            and ta.data_3= #{data_3}
        </if>
        <if test="test_time != null and test_time !=''">
            and tp.test_time= #{test_time}
        </if>
    </where>
</select>