使用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("

  SELECT 

    Quiz_Start,

    Quiz_End,

    Marks

  FROM quiz_record

  WHERE Student_Id = :student_id  

");

// 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("

  SELECT 

    COUNT(*) as quiz_count

  FROM quiz_record

  WHERE 

    Marks < :mark_limit OR

    (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 =  abs($quiz_end->getTimestamp() - $quiz_start->getTimestamp());

$count_res = $count_statement->execute([

  ':mark_limit' => $selected_quiz['Marks'], 

  ':seconds_diff' => $diff_in_seconds

]);

$fetched = $count_res->fetch(PDO::FETCH_ASSOC);

// $count should be the count

$count = $fetched['quiz_count'];

请注意，此代码应位于您的数据层中。如果提供更多详细信息，我可以详细介绍。