left join给左表加筛选条件,拼接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 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");  				//假条提交时间