Laravel动态SQL
$stay_at_school_id = request()->stay_at_school_id == null || request()->stay_at_school_id == "null" || request()->stay_at_school_id == "" ? null : (int)request()->stay_at_school_id;
$check_state = request()->check_state == null || request()->check_state == "null" || request()->check_state == "" ? null : (int)request()->check_state;
$result = StayAtSchoolDetails::with('stayAtSchool')
->with('myBed')->with('applyBed')
->when($stay_at_school_id, function ($query) use ($stay_at_school_id) {
return $query->whereHas('stayAtSchool', function ($query) use ($stay_at_school_id) {
return $query->where('id', '=', $stay_at_school_id);
});
})
->when($check_state==null, function ($query) use ($check_state) {
return $query->where('check_state', '=', $check_state);
})
->get();
查询关联的属性值
->whereHas('bed',function ($query) use ($bed_id){ $query->where('id','=',$bed_id); })
function exportStudentData($request){
$name = $request['name']!="null"?substr($request['name'],1):null;
$bed_id = $request['bed_id']!="null"?$request['bed_id']:null;
$number = $request['number']!="null"?substr($request['number'],1):null;
$nationality = $request['nationality']!="null"?substr($request['nationality'],1):null;
$politics = $request['politics']!="null"?substr($request['politics'],1):null;
$college = $request['college']!="null"?substr($request['college'],1):null;
$major = $request['major']!="null"?substr($request['major'],1):null;
$class = $request['class']!="null"?substr($request['class'],1):null;
$grade = $request['grade']!="null"?substr($request['grade'],1):null;
$system = $request['system']!="null"?substr($request['system'],1):null;
$level = $request['level']!="null"?$request['level']:null;
$queryUserStudent =UserStudent::query()
->with('bed')
->when($name,function ($query) use ($name){
$query->where('name','like',$name);
})
->when($number,function ($query) use ($number){
$query->where('number','like',$number);
})
->when($nationality,function ($query) use ($nationality){
$query->where('nationality','like',$nationality);
})
->when($politics,function ($query) use ($politics){
$query->where('politics','like',$politics);
})
->when($college,function ($query) use ($college){
$query->where('college','like',$college);
})
->when($major,function ($query) use ($major){
$query->where('major','like',$major);
})
->when($class,function ($query) use ($class){
$query->where('class','like',$class);
})
->when($grade,function ($query) use ($grade){
$query->where('grade','like',$grade);
})
->when($system,function ($query) use ($system){
$query->where('system','like',$system);
})
->when($level,function ($query) use ($level){
$query->where('level','=',$level);
})
->get();
return $queryUserStudent;
}
java 动态SQL
<select id="selectAnswerList" parameterType="com.ruoyi.course.domain.Answer" resultMap="AnswerResult">
select id, exam_id, student_number, content, score, create_time, update_time, delete_time,comment,state from answer
<where>
<if test="examId != null "> and exam_id = #{examId}</if>
<if test="studentNumber != null and studentNumber != ''"> and student_number = #{studentNumber}</if>
<if test="content != null and content != ''"> and content = #{content}</if>
<if test="score != null "> and score = #{score}</if>
<if test="deleteTime != null "> and delete_time = #{deleteTime}</if>
<if test="comment != null "> and comment = #{comment}</if>
<if test="state != null "> and state = #{state}</if>
</where>
</select>
<insert id="insertAnswer" parameterType="Answer" useGeneratedKeys="true" keyProperty="id">
insert into answer
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="examId != null ">exam_id,</if>
<if test="studentNumber != null and studentNumber != ''">student_number,</if>
<if test="content != null ">content,</if>
<if test="score != null ">score,</if>
<if test="createTime != null ">create_time,</if>
<if test="updateTime != null ">update_time,</if>
<if test="deleteTime != null ">delete_time,</if>
<if test="comment != null ">comment,</if>
<if test="state != null ">state,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="examId != null ">#{examId},</if>
<if test="studentNumber != null and studentNumber != ''">#{studentNumber},</if>
<if test="content != null ">#{content},</if>
<if test="score != null ">#{score},</if>
<if test="createTime != null ">#{createTime},</if>
<if test="updateTime != null ">#{updateTime},</if>
<if test="deleteTime != null ">#{deleteTime},</if>
<if test="comment != null ">#{comment},</if>
<if test="state != null ">#{state},</if>
</trim>
</insert>
<update id="updateAnswer" parameterType="com.ruoyi.course.domain.Answer">
update answer
<trim prefix="SET" suffixOverrides=",">
<if test="examId != null ">exam_id = #{examId},</if>
<if test="studentNumber != null and studentNumber != ''">student_number = #{studentNumber},</if>
<if test="content != null ">content = #{content},</if>
<if test="score != null ">score = #{score},</if>
<if test="createTime != null ">create_time = #{createTime},</if>
<if test="updateTime != null ">update_time = #{updateTime},</if>
<if test="deleteTime != null ">delete_time = #{deleteTime},</if>
<if test="comment != null ">comment = #{comment},</if>
<if test="state != null ">state = #{state},</if>
</trim>
where id = #{id}
</update>
select * from answer
where student_number in
(select student_number from answer group by student_number,exam_id having count(student_number) > 1)
and exam_id in (SELECT exam_id from answer group by exam_id, student_number having count(exam_id) > 1)
;
|