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

问题描述

有如下两张表

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>系统当前时间

问题解答

<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>