php - 如何计算所有在场学生的百分比

Question

有两个表要一起工作。

第一个表:

AttendanceID TeacherID BatchID SubjectID SemesterID Date 
          32       110       8         9          1 2016-08-04 
          31       102       8        10          1 2016-07-17 
          30       108       6        22          3 2016-06-27 
          29       109       7        18          2 2016-06-27 
          28       109       8        13          1 2016-06-27 
          27       110       7         7          2 2016-06-27 
          26       110       8         9          1 2016-06-27 
          25       104       2        42          7 2016-04-20 
          24       104       5        35          5 2016-04-14 
          23       104       2        42          7 2016-04-14
          22       102       2        41          7 2016-04-13 
          21       102       2        41          7 2016-04-10 
          20       102       6        23          3 2016-04-10 
          19       102       6        23          3 2016-04-10

第二张表:

第一个表由唯一的行组成，这些行将用于表二中的每个新出席列表。

例如:在第一个表中，AttendanceID 21 在第二个表中用于特定主题的完整出勤。

我想计算特定Subject的第二个表中所有学生的百分比，总数可以通过第一个表AttendanceID

我在 PHP 中所做的是:首先，我使用此查询从第一个表中获取总数:

SELECT COUNT(AttendanceID) FROM attendances WHERE SubjectID = ? AND BatchID = ?"

一旦我从第一个表中获得特定主题和批处理的总出席人数，我将其存储在变量 $total 中，然后我编写另一个查询以从第二个表中获取获得的出席人数:

SELECT COUNT(AttendanceDetailID) FROM attendancedetail WHERE CollegeID = ? AND Status = 'present' AND SubjectID = ?"

获得出勤率后，我将其存储在变量 $obtained 中一旦我获得了这两个值，我就会像这样计算 PHP 中的百分比:

    if(!empty($total) && !empty($obtained)) {
    $result = (($obtained * 100)/ $total);
        $result = round($result);
    }

完整的PHP代码如下:

    public function showStateOfAttendance($subjectID, $batchID){

 $st = $this->conn->prepare("SELECT CollegeID, Name, Gender, Photo FROM students WHERE BatchID = ?");
    $st->bind_param("i", $batchID);
    $st->execute();
    $st->store_result();
    $num_rows = $st->num_rows;
    $st->bind_result($college_id, $name, $gender, $photo);


    $this->response['attendance'] = array();
    while($st->fetch()) {

        $this->calcultaionOfAttendance($subjectID, $college_id, $name,  $gender, $photo, $batchID);



    }
     return json_encode($this->response);
    $st->free_result();
    $st->close();

}


public function calcultaionOfAttendance($subjectID, $studentID, $name, $gender, $photo,  $batchID) {

      $stmt = $this->conn->prepare("SELECT COUNT(AttendanceID) FROM attendances WHERE SubjectID = ? AND BatchID = ?");
    $stmt->bind_param("ii", $subjectID, $batchID);
    $stmt->execute();
    $stmt->store_result();
    $num_rows = $stmt->num_rows;
    $stmt->bind_result($AttendanceID);
    while($stmt->fetch()) {
       $total = $AttendanceID;  
    }
    $stmt->free_result();
    $stmt->close();

    $stmt2 = $this->conn->prepare("SELECT COUNT(AttendanceDetailID) FROM attendancedetail WHERE CollegeID = ? AND Status = 'present' AND SubjectID = ?");
    $stmt2->bind_param("ii", $studentID, $subjectID);
    $stmt2->execute();
    $stmt2->store_result();
    $stmt2->bind_result($AttendanceDetailID);
    while($stmt2->fetch()){
        $obtained = $AttendanceDetailID;
    }
    if(!empty($total) && !empty($obtained)) {
    $result = (($obtained * 100)/ $total);
        $result = round($result);
       $rating = ($result)/20;

         $tmp = array();

          $tmp['result'] = $result;
          $tmp['total'] = $total;
          $tmp['obtained'] = $obtained;
          $tmp['rating'] = $rating;
          $tmp['name'] = $name;
          $tmp['college_id'] = $studentID;
          $tmp['gender'] = $gender;
          $tmp['photo'] = $photo;

        array_push($this->response['attendance'],$tmp);



    //var_dump(array($total, $obtained, $result, $rating, $studentID, $name));
    }else if(empty($total)) {

         $tmp = array();

          $tmp['result'] = 0.0;
          $tmp['total'] = 0.0;
          $tmp['obtained'] = $obtained;
          $tmp['rating'] = 0.0;
          $tmp['name'] = $name;
          $tmp['college_id'] = $studentID;
        array_push($this->response['attendance'],$tmp);


    //var_dump(array("0.0",$obtained, "0.0","0.0",$studentID,$name));
    }else if(empty($obtained)) {

         $tmp = array();

          $tmp['result'] = 0.0;
          $tmp['total'] = $total;
          $tmp['obtained'] = 0.0;
          $tmp['rating'] = 0.0;
          $tmp['name'] = $name;
          $tmp['college_id'] = $studentID;
        array_push($this->response['attendance'],$tmp);


    //var_dump(array($total, "0.0", "0.0","0.0", $studentID , $name));
    }


}

这是我所做的查询的 android 屏幕截图:以下结果是针对 SubjectID = 23 和 BatchID = 6

它为我提供了所需的结果，但我需要更好的方法来计算它，是否可以通过单个查询来实现？

谢谢

Answer 1

尝试:

    SELECT  s.CollectID, s.Name, s.Gender, s.Photo, 
        (SELECT count(AttendanceID) from attendances WHERE SubjectID =? and BatchID = s.BatchID) as total,
        (SELECT count(AttendanceDetailID) FROM attendancedetail WHERE CollegeID = s.CollectID and Status = 'present' and SubjectID = ?) as obtained
 FROM students s
 WHERE s.BatchID = ?