php - 如何计算内连接上两个表之间重复值的总数

Question

我有以下查询

    SELECT fixtures.Fixture_ID, fixtures.Home_Score,
 fixtures.Away_Score, predict.Fixture_ID, predict.pHome_Score, predict.pAway_Score 

    FROM fixtures INNER JOIN predict 
ON fixtures.Fixture_ID=predict.Fixture_ID

我想统计满足以下条件的次数

 fixtures.Home_Score=predict.pHome_Score 
AND fixtures.Away_Score=predict.pAway_Score 
AND fixtures.Fixture_ID=predict.Fixture_ID

我尝试使用“COUNT()”，然后使用“Having count()>1”，但无法使语法正常工作

我还尝试计算以下 php.ini 中满足 if 条件的次数。我不确定这是否可行，所以我认为计数可能必须在 as SQL 语句内完成

<?php


     $current = $user->data()->id;



                        $sql2 = "SELECT fixtures.Home_team, fixtures.Away_Team, fixtures.Home_Score, fixtures.Away_Score, predict.pHome_Score, predict.pAway_Score FROM fixtures 
INNER JOIN predict 
ON fixtures.Fixture_ID=predict.Fixture_ID WHERE predict.id='".$current."'";                 

    echo "The number of detected predictions:", '<br>';

                        $predictions = DB::getInstance()->query($sql2);

                            foreach ($predictions->results() as $rows) {
                            $rows= get_object_vars($rows);
                             $num_rows= $predictions->count();
                            }

                         for($count=0;$count<$num_rows;$count++){


                            $r_home_score = $predictions->results()[$count]->Home_Score;
                            $p_home_score = $predictions->results()[$count]->pHome_Score;
                            $r_away_score = $predictions->results()[$count]->Away_Score;
                            $p_away_score = $predictions->results()[$count]->pAway_Score;

                             $p=0;

                                if($r_home_score==$p_home_score&&$r_away_score==$p_away_score){


                                    $p++;

                                    echo $p;


                                    }


                            }

输出为:

检测到的预测数量:111111

我想输出6

Answer 1

如果您只想从数据库返回单个计数，您可以执行如下查询:

  SELECT COUNT(*) AS mycount
    FROM fixtures f 
    JOIN predict p
      ON p.Fixture_ID  = f.Fixture_ID
     AND p.pHome_Score = f.Home_Score
     AND p.pAway_Score = f.Away_Score

<小时/>

跟进

  $dbh = DB::getInstance();
  $sql = "SELECT COUNT(*) AS mycount
            FROM fixtures f 
            JOIN predict p
              ON p.Fixture_ID  = f.Fixture_ID
             AND p.pHome_Score = f.Home_Score
             AND p.pAway_Score = f.Away_Score
           WHERE p.id = ?"; 
  if ($sth = $dbh->prepare($sql)) {
      $sth->bindParam(1, $current, PDO::PARAM_INT);
      if ($sth->execute()) {
          if ($row = $sth->fetch(PDO::FETCH_ASSOC)) {

              echo $row['mycount'];

          } else {
              // this should never happen with a COUNT(*) query
              echo "query returned 0 rows";
          }
      } else {
          echo "PDO error on execute: ";
          print_r($dbh->errorInfo());
  } else {
      echo "PDO error on prepare: ";
      print_r($dbh->errorInfo());
  }