PHP和JAVA 动态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) {
            //whereHas 对象中对象属性可以用这个
                return $query->whereHas('stayAtSchool', function ($query) use ($stay_at_school_id) {
                    return $query->where('id', '=', $stay_at_school_id);
                });
            })
            //when(判断条件，回调函数)，对象的属性值
            ->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')
// 	业务不需要这个，用上一行代替。
//            ->whereHas('bed',function ($query) use ($bed_id){
//                $query->where('id','=',$bed_id);
//            })
            ->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;
    }
    

    
        select id, exam_id, student_number, content, score, create_time, update_time, delete_time,comment,state from answer
          
             and exam_id = #{examId}
             and student_number = #{studentNumber}
             and content = #{content}
             and score = #{score}
             and delete_time = #{deleteTime}
             and comment = #{comment}
             and state = #{state}
        
    

    
        insert into answer
        
            exam_id,
            student_number,
            content,
            score,
            create_time,
            update_time,
            delete_time,
            comment,
            state,
         
        
            #{examId},
            #{studentNumber},
            #{content},
            #{score},
            #{createTime},
            #{updateTime},
            #{deleteTime},
            #{comment},
            #{state},
         
    


    
        update answer
        
            exam_id = #{examId},
            student_number = #{studentNumber},
            content = #{content},
            score = #{score},
            create_time = #{createTime},
            update_time = #{updateTime},
            delete_time = #{deleteTime},
            comment = #{comment},
            state = #{state},
        
        where id = #{id}
    

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)
;