来自数据库的数据不正确
来自数据库的数据不正确
提问于 2019-07-21 22:54:56
我尝试制作学生成绩管理系统。当我试图给出位置时，我面临的问题是，每个学生都依赖于他们在考试中的总成绩。在我的输出中，我得到了第一(获得分数450)，第二(获得分数449)，第四(获得分数448)。缺少第3个位置。第五，第十和其他一些位置也丢失了。但我在我的代码中没有发现任何问题。
我的数据库
if (!function_exists('get_position_in_exam')) {
function get_position_in_exam($school_id, $exam_id, $class_id, $section_id, $mark) {
$ci = & get_instance();
$sql = "SELECT id, total_obtain_mark, FIND_IN_SET( total_obtain_mark,(
SELECT GROUP_CONCAT( total_obtain_mark ORDER BY total_obtain_mark DESC )
FROM exam_results WHERE school_id = $school_id AND exam_id = $exam_id AND class_id = $class_id AND section_id = $section_id ))
AS rank
FROM exam_results
WHERE school_id = $school_id AND exam_id = $exam_id AND class_id = $class_id AND section_id = $section_id AND total_obtain_mark = $mark";
$rank = @$ci->db->query($sql)->row()->rank;
if($mark == 0){
return '--';
}
if($rank == 1){
return $rank.'st';
}elseif($rank == 2){
return $rank.'nd';
}elseif($rank == 3){
return $rank.'rd';
}elseif($rank > 3 ){
return $rank.'th';
}else{
return '--';
}
}
}回答 1
Stack Overflow用户
回答已采纳
发布于 2019-07-22 00:21:40
子查询中需要distinct total_obtain_mark
GROUP_CONCAT( distinct total_obtain_mark ORDER BY total_obtain_mark DESC )
"SELECT id, total_obtain_mark, FIND_IN_SET( total_obtain_mark,(
SELECT GROUP_CONCAT( distinct total_obtain_mark ORDER BY total_obtain_mark DESC )
FROM exam_results WHERE school_id = $school_id AND exam_id = $exam_id AND class_id = $class_id AND section_id = $section_id ))
AS rank
FROM exam_results
WHERE school_id = $school_id AND exam_id = $exam_id AND class_id = $class_id AND section_id = $section_id AND total_obtain_mark = $mark";否则，如果你有一些排名相同的学生，你就会跳过这个职位
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/57134317
复制相关文章
相似问题