使用where子句计算sql数据的行数

Question

quiz_record 桌子：

我需要计算所有那些具有排marks 不到的marks的Student_Id['4']。

此外，如果其他的标记是关闭相同的的标记Student_Id['4']，然后还指望所有那些time gap (Quiz_End - Quiz_Start)是更比time gap (Quiz_End - Quiz_Start)的Student_Id['4']。

预期结果：2

为此，我尝试了：

$time_taken = strtotime($fetch_quiz_record['Quiz_End']) - strtotime($fetch_quiz_record['Quiz_Start']);

$count_less_played = $user->runQuery("SELECT COUNT(Id) AS Id FROM quiz_record WHERE Quiz_Id=:quiz_id AND Marks<=:marks AND (Quiz_End - Quiz_Start) >:time_diff");

$count_less_played->bindparam(":quiz_id",$fetch_quiz_record['Quiz_Id']);

$count_less_played->bindparam(":marks",$fetch_quiz_record['Marks']);

$count_less_played->bindparam(":time_diff",$time_taken);

$count_less_played->execute();

$count_less_played_cnt = $count_less_played->fetch(PDO::FETCH_ASSOC);

echo $count_less_played_no= $count_less_played_cnt['Id'];

输出：4

Answer 1

试试这个（评论中的解释）：$time_taken = strtotime($fetch_quiz_record['Quiz_End']) - strtotime($fetch_quiz_record['Quiz_Start']);/////--Counting Rows which have less than marks--/////$count_less_marks = $user->runQuery("SELECT COUNT(Id) AS Id FROM quiz_record WHERE Quiz_Id=:quiz_id AND Marks<:marks");$count_less_marks->bindparam(":quiz_id",$fetch_quiz_record['Quiz_Id']);$count_less_marks->bindparam(":marks",$fetch_quiz_record['Marks']);$count_less_marks->execute();$count_less_marks_cnt = $count_less_marks->fetch(PDO::FETCH_ASSOC);$count_less_marks_no= $count_less_marks_cnt['Id'];/////--Counting Rows which have equal marks and time difference more--/////$count_more_time = $user->runQuery("SELECT COUNT(Id) AS Id FROM quiz_record WHERE Quiz_Id=:quiz_id AND Marks=:marks AND TIMESTAMPDIFF(SECOND, Quiz_Start, Quiz_End) > :time_diff");$count_more_time->bindparam(":quiz_id",$fetch_quiz_record['Quiz_Id']);$count_more_time->bindparam(":marks",$fetch_quiz_record['Marks']);$count_more_time->bindparam(":time_diff",$time_taken);$count_more_time->execute();$count_more_time_cnt = $count_more_time->fetch(PDO::FETCH_ASSOC);$count_more_time_no= $count_more_time_cnt['Id'];/////--Add both counts, to get required results--/////$count_less_played = $count_less_marks_no + $count_more_time_no;echo $count_less_played;

Answer 2

首先，你能指定 $user 和 $fetch_quiz_record 是什么吗？如果没有这些信息，我会先选择参考，在您的情况下是 quiz_record，Student_Id 为 4。$statement = $conn->prepare("&nbsp; SELECT&nbsp;&nbsp; &nbsp; Quiz_Start,&nbsp; &nbsp; Quiz_End,&nbsp; &nbsp; Marks&nbsp; FROM quiz_record&nbsp; WHERE Student_Id = :student_id&nbsp;&nbsp;");// in your case $student_id would be 4$res = $statement->execute([':student_id' => $student_id]);$selected_quiz = $res->fetch(PDO::FETCH_ASSOC);$count_statement = $conn->prepare("&nbsp; SELECT&nbsp;&nbsp; &nbsp; COUNT(*) as quiz_count&nbsp; FROM quiz_record&nbsp; WHERE&nbsp;&nbsp; &nbsp; Marks < :mark_limit OR&nbsp; &nbsp; (Marks = :mark_limit AND TIMESTAMPDIFF(SECOND, Quiz_Start, Quiz_End > :seconds_diff))");// depending on the datatype of Quiz_Start and Quiz_End$quiz_start = \DateTimeImmutable::createFromFormat("Y-m-d H:i:s", $selected_quiz['Quiz_Start']);$quiz_end = \DateTimeImmutable::createFromFormat("Y-m-d H:i:s", $selected_quiz['Quiz_End']);$diff_in_seconds =&nbsp; abs($quiz_end->getTimestamp() - $quiz_start->getTimestamp());$count_res = $count_statement->execute([&nbsp; ':mark_limit' => $selected_quiz['Marks'],&nbsp;&nbsp; ':seconds_diff' => $diff_in_seconds]);$fetched = $count_res->fetch(PDO::FETCH_ASSOC);// $count should be the count$count = $fetched['quiz_count'];请注意，此代码应位于您的数据层中。如果提供更多详细信息，我可以详细介绍。