我在Laravel Framework中使用了以下查询
$student_attendance_search_result = DB::table('tbl_student_attendance')
->join('tbl_student_admission', 'tbl_student_attendance.student_id', '=', 'tbl_student_admission.student_id')
->select(DB::raw('tbl_student_attendance.*,tbl_student_admission.student_id,tbl_student_admission.student_full_name_english,tbl_student_admission.class,tbl_student_admission.section,tbl_student_admission.roll_no, count(case when attendance_status ="Absent" then 1 end) as total_absent,count(case when attendance_status ="Present" then 1 end) as total_present,count(case when attendance_status ="Leave" then 1 end) as total_leave,count(distinct date) as total_class'))
->where('tbl_student_attendance.academic_year', $academic_year)
->where('tbl_student_admission.class', $classs)
->where('tbl_student_admission.section', $section)
->where('tbl_student_attendance.month', $month)
->groupBy('tbl_student_admission.student_id')
->get();
当我试图运行它时，它抛出以下异常
SQLSTATE [42000]：语法错误或访问冲突：1055'db_smsfinal1user.tbl_student_attendance.student_attendance_id'不在GROUP BY中（SQL：select tbl_student_attendance . *，tbl_student_admission.student_id，tbl_student_admission.student_full_name_english，tbl_student_admission.class，tbl_student_admission.section， tbl_student_admission.roll_no，count（attendance_status =“Absent”然后1结束时的情况）作为total_absent，count（attendance_status =“Present”然后1结束时的情况）作为total_present，count（attendance_status =“Leave”然后1结束时的情况）as total_leave，计数从tbl_student_attendance内上tbl_student_attendance.student_id = tbl_student_admission.student_id加入tbl_student_admission（不同日期）作为total_class其中tbl_student_attendance.academic_year = 2018和tbl_student_admission.class = 2和由tbl_student_admission tbl_student_admission.section =无和tbl_student_attendance.month =九月基 . 学生卡）
知道我做错了什么吗？
1 回答
使用DB :: raw作为选择参数
DB::select( DB::raw('student_id,count(case when attendance_status ='Absent' then 1 end) as absent_count,count(case when attendance_status ='Present' then 1 end) as present_count,count(case when attendance_status ='Leave' then 1 end) as leave_count,count(distinct date) as Tot_count') )->from(..)->groupBy(...);