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 on code1.code=leave_note.type_code and code1.type='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 leave_note.tea_id=? ");
}else{
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"); //假条提交时间