leftjoin给左表加筛选条件，拼接sql
left join 就是⽆论on中的条件如何都会选出左表的数据，如果要屏蔽左表的⼀条数据，⽐如屏蔽左表deleted=1的数据怎么办？
这个问题可以通过拼接stringBuffer来实现。如下，需要注意的是需要给⼦查询起⼀个名字，不然就会报错Every derived table must have its own alias。
StringBuffer sql = new StringBuffer();//拼接条件sql
StringBuffer exeSql = new StringBuffer();//执⾏sql
sql.append("select leave_note.id,"  //假条id
+ " stu.stu_name,"      //请假⼈姓名
+ " content,"      //请假原因描述
+ " type_code,"      //假条类型码(事假/病假)
+ " leave_note.deleted,"
+ " code1.value type_value,"    //假条类型值
+ " leave_note.add_time"      //假条提交时间
+ " from stu_leave_note leave_note"  //假条表
+ " left join stu_information stu on leave_note.stu_id=stu.id"  //学⽣信息表
+ " left join code code1 de=pe_code pe='leave_note_type'");
//增加假条状态筛选
if(status_code.equals("checked")){
sql.append(" and leave_note.status_code='leave_note_status002' or leave_note.status_code='leave_note_status003'");
}else{
sql.append(" and leave_note.status_code='leave_note_status001' ");
}
//增加⽤户筛选
if(user_type_code.equals("user002")){
sql.append(" and a_id=? ");
}else{
sql left join 多表连接sql.append(" and leave_note.stu_id=? ");
}
exeSql.append("select leave_list.id,"  //假条id
+ " stu_name,"      //请假⼈姓名
+ " content,"      //请假原因描述
+ " type_code,"      //假条类型码(事假/病假)
+ " leave_list.deleted,"
+ " type_value,"    //假条类型值
+ " leave_list.add_time from("+sql
+ " ) as leave_list where leave_list.deleted=0");      //假条提交时间

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。