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>