如何使用for循环和union all分组

Question

Hi this is my query from this query i am getting 3 records i want only one record and i am trying to group by studentid

foreach ($gdFinaldata['gdskill'] as $skillId => $skillScore){

$filterQuery .= $union_all. "SELECT stu.student_pid

FROM tbl_students stu LEFT JOIN r_job_invitations jbi ON stu.student_email = jbi.email 

LEFT JOIN r_job_scores jsc  ON jsc.student_id = stu.student_pid 

WHERE job_id = ".$jobID."  AND skill_id = ".$skillId." AND gd_score >= ".$skillScore." ";

$union_all=" UNION ALL ";

} //for ends here

the recodrs will come like this:

SELECT stu.student_pid FROM tbl_students stu LEFT JOIN r_job_invitations jbi ON stu.student_email = jbi.email LEFT JOIN r_job_scores jsc ON jsc.student_id = stu.student_pid WHERE job_id = 88 AND skill_id = 3 AND gd_score >= 1   
UNION ALL
SELECT stu.student_pid FROM tbl_students stu LEFT JOIN r_job_invitations jbi ON stu.student_email = jbi.email LEFT JOIN r_job_scores jsc ON jsc.student_id = stu.student_pid WHERE job_id = 88  AND skill_id = 63 AND gd_score >= 2   
UNION ALL  
SELECT stu.student_pid FROM tbl_students stu LEFT JOIN r_job_invitations jbi ON stu.student_email = jbi.email LEFT JOIN r_job_scores jsc ON jsc.student_id = stu.student_pid WHERE job_id = 88  AND skill_id = 128 AND gd_score >= 3

After this i want to add group by how can i do that i tried like this:

$groupby = "GROUP BY student_id";
$filterStudents = $conn->query($filterQuery.$groupby);

My issue is still returning 3 records i want to group by student

For Reference:

Answer 1

There are several ways to do this.

• Use union instead of union all, this will retain only unique entries

  select ...
  union
  select ...
  union
  select ...
  

• If you want to keep union all (why do you?), use parenthesis around your selects and append group by after it

  select student_pid
  ( select student_pid, student_id from ...
  union all
  select student_pid, student_id from ...
  union all
  select student_pid, student_id from ... ) as t
  group by student_id
  

  Your PHP would then look like

  foreach (...) {
      $filterQuery .= $union_all. "SELECT stu.student_pid, jsc.student_id ..."
      // ...
  }
  
  $outerQuery = "select student_pid ("
  $groupby = ") as t GROUP BY student_id";
  $filterStudents = $conn->query($outerQuery . $filterQuery . $groupby);
  

• Instead of group by, you can also use distinct, e.g.

  select distinct student_pid
  ( select student_pid from ...
  union all
  select student_pid from ...
  union all
  select student_pid from ... ) as t
  

• Collect the result in an array and then use array_unique Although this is the least efficient, since it fetches all entries instead of the relevant parts.