douju2053 2017-01-06 04:36
浏览 446
已采纳

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

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: enter image description here

  • 写回答

1条回答 默认 最新

  • dongxue7306 2017-01-06 04:51
    关注

    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.

    展开全部

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥30 在CodBlock上用c++语言运行
  • ¥15 求C6748 IIC EEPROM程序固化烧写算法
  • ¥50 关于#php#的问题,请各位专家解答!
  • ¥15 python 3.8.0版本,安装官方库ibm_db遇到问题,提示找不到ibm_db模块。如何解决?
  • ¥15 TMUXHS4412如何防止静电,
  • ¥30 Metashape软件中如何将建模后的图像中的植被与庄稼点云删除
  • ¥20 机械振动学课后习题求解答
  • ¥15 IEC61850 客户端和服务端的通讯机制
  • ¥15 MAX98357a(关键词-播放音频)
  • ¥15 Linux误删文件,请求帮助
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部