PHP SQLSTATE [42000]语法错误或访问冲突：在查询

问题描述：

我运行一个查询，但是当我运行它，它会返回一个PHP SQLSTATE [42000]语法错误或访问冲突：在查询

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' count(distinct exams.user_id) as count_user_mhs from `users` where `users.type`' at line 1 (SQL: select `universities`.`name`, count(distinct exams.user_id) as count_user_dsn from `users` where `users.type` = 0, count(distinct exams.user_id) as count_user_mhs from `users` where `users.type` = 1, count(exams.subject_id) as count_subject, sum(exams.score) as count_score, TIME_FORMAT(SUM(TIMEDIFF(exams.exam_end_date, exams.exam_start_date)), "%H:%i:%s") as count_time from `exams` inner join `universities` on `universities`.`id` = `exams`.`university_id` inner join `users` on `users`.`id` = `exams`.`user_id` where exists (select * from `universities` where `exams`.`university_id` = `universities`.`id`) group by `user_id` order by `count_score` desc, `count_time` asc)

$exams = Exam::join('universities', 'universities.id', '=', 'exams.university_id') 
       ->join('users', 'users.id', '=', 'exams.user_id') 
       ->select('universities.name',DB::raw('count(distinct exams.user_id) as count_user_dsn from `users` where `users.type` = 0'),DB::raw('count(distinct exams.user_id) as count_user_mhs from `users` where `users.type` = 1'),DB::raw('count(exams.subject_id) as count_subject'),DB::raw('sum(exams.score) as count_score'),DB::raw('TIME_FORMAT(SUM(TIMEDIFF(exams.exam_end_date, exams.exam_start_date)), "%H:%i:%s") as count_time'));

谁能帮助1064？我猜测，这与DB::raw表达式有关。但我不知道到底出了什么问题。

1）这不是一个警告，这是一个错误。 2）你没有复制整个错误信息，所以我们甚至不知道可能发生了什么问题。 3）你还没有共享从你的代码创建的sql查询laravel，这也有助于识别错误。但是那些原始sql部分中的计数子查询看起来是错误的。 4）如果你描述了你想要达到的目标，这也会有所帮助。因为我们可以告诉你犯了什么错误是一回事，但另一个想法是帮助你创建一个工作解决方案。 – Shadow

[进一步阅读]（https://stackoverflow.com/questions/23515347/how-cani-i-fix-mysql-error-1064）。 – tadman

粘贴普通错误消息，它会帮助调试。 – BDS

答

你的生成的查询看起来像这样：

select 
    `universities`.`name`, 
    count(distinct exams.user_id) as count_user_dsn from `users` where `users.type` = 0, 
    count(distinct exams.user_id) as count_user_mhs from `users` where `users.type` = 1, 
    count(exams.subject_id) as count_subject, 
    sum(exams.score) as count_score, 
    TIME_FORMAT(SUM(TIMEDIFF(exams.exam_end_date, exams.exam_start_date)), "%H:%i:%s") as count_time 
from 
    `exams` 
     inner join 
    `universities` on `universities`.`id` = `exams`.`university_id` 
     inner join 
    `users` on `users`.`id` = `exams`.`user_id` 
where 
    exists (select * from `universities` where `exams`.`university_id` = `universities`.`id`) 
group by 
    `user_id` 
order by 
    `count_score` desc, 
    `count_time` asc

的问题是在其上由所述DB::raw代码生成线3和4。

要解决这个问题，你需要更新你这样的雄辩查询：

<?php 
$exams = Exam::join('universities', 'universities.id', '=', 'exams.university_id') 
    ->join('users', 'users.id', '=', 'exams.user_id') 
    ->select(
     'universities.name', 
     DB::raw('(select count(distinct exams.user_id) as count_user_dsn from `users` where `users.type` = 0)'), 
     DB::raw('(select count(distinct exams.user_id) as count_user_mhs from `users` where `users.type` = 1)'), 
     DB::raw('count(exams.subject_id) as count_subject'),DB::raw('sum(exams.score) as count_score'), 
     DB::raw('TIME_FORMAT(SUM(TIMEDIFF(exams.exam_end_date, exams.exam_start_date)), "%H:%i:%s") as count_time') 
    );

也就是说......在主SELECT语句执行的SELECT COUNT子查询是不是一个好主意，因为他们会为每个被执行由主查询选择的行。

PHP SQLSTATE [42000]语法错误或访问冲突：在查询

相关推荐