如何从具有不同条件的表中多次选择同一列

问题描述

我有一张桌子：



id student_regno subject_name student_mark类别

1 OIS001英语7作业

2 OIS001 Agric.Sc 6作业

3 OIS001英语15考试

4 OIS001 Agric.Sc 13考试

5 OIS001英语50考试

6 OIS001 Agric.Sc 48考试





我想获取以下记录。



主题名称分配测试考试

英语7 15 50

Agric.Sc 6 13 48





Appreciaite你的帮助。请告诉我这是否可行？如果是，请提供示例SQL。



谢谢



我有什么尝试过：



我使用了以下代码：

  $ sql  =   SELECT student_regno，student_name，academic_session，academic_term，class_abbrev，class_group，subject_name ，student_mark，form_teacher FROM academic_scores WHERE student_regno ='$ student_regno'AND class_name ='$ class_name'AND class_group ='$ class_group'AND academic_session ='$ academic_session'AND academic_term ='$ academic_term'GROUP BY subject_name; 
  $ result  = mysqli_query（$ conn，$ sql）; 
 
 
 while（$ row = $ result-> fetch_assoc（））{
 
 $ subject = $ row ['  subject_name']; 
 
  $ sql1  =   SELECT student_mark FROM academic_scores WHERE student_regno ='$ student_regno'AND class_name ='$ class_name'AND class_group ='$ class_group'AND academic_session ='$ academic_session'AND academic_term ='$ academic_term'AND category ='Assignment' ; 
  $ ress  = mysqli_query（$ conn，$ sql1）; 
 $ row1 = $ ress-> fetch_assoc（）; 
 $ assignment = $ row1 ['  student_mark']; 
 
  $ sql2  =   SELECT student_mark FROM academic_scores WHERE student_regno ='$ student_regno'AND class_name ='$ class_name'AND class_group ='$ class_group'AND academic_session ='$ academic_session'AND academic_term ='$ academic_term'AND category ='Test' ; 
  $ res  = mysqli_query（$ conn，$ sql2）; 
 $ row2 = $ res-> fetch_assoc（）; 
 $ test = $ row2 ['  student_mark']; 
 
  $ sql3  =   SELECT student_mark FROM academic_scores WHERE student_regno ='$ student_regno'AND class_name ='$ class_name'AND class_group ='$ class_group'AND academic_session ='$ academic_session'AND academic_term ='$ academic_term'AND category ='Examination' ; 
  $ res  = mysqli_query（$ conn，$ sql3）; 
 $ row3 = $ res-> fetch_assoc（）; 
 $ exam = $ row3 ['  student_mark']; 
 
 
 
 
  echo  ' < tr> 
< td>'。$ subject。' < / td> 
< td>'。$ assignment。' < / td> 
< td>'。$ test。' < / td> 
< td>'。$ exam。' < / td> 
 
< / tr>'; 
 
 
} 
} 

解决方案

I have a table below:

id student_regno subject_name student_mark category
1 OIS001 English 7 Assignment
2 OIS001 Agric.Sc 6 Assignment
3 OIS001 English 15 Test
4 OIS001 Agric.Sc 13 Test
5 OIS001 English 50 Exam
6 OIS001 Agric.Sc 48 Exam


I want to fetch records as following.

SUBJECT NAME ASSIGNMENT TEST EXAM
English 7 15 50
Agric.Sc 6 13 48


Appreciaite your help. Please tell me if this is possible or not? If yes, can you please provide the sample SQL.

Thanks

What I have tried:

I used the following codes:

$sql = "SELECT student_regno, student_name, academic_session, academic_term, class_abbrev, class_group, subject_name, student_mark,form_teacher FROM academic_scores WHERE student_regno ='$student_regno' AND class_name = '$class_name' AND class_group='$class_group' AND academic_session = '$academic_session' AND academic_term = '$academic_term' GROUP BY subject_name";
       $result = mysqli_query($conn,$sql);


while($row = $result->fetch_assoc()){

        $subject=$row['subject_name'];

        $sql1 = "SELECT student_mark FROM academic_scores WHERE student_regno ='$student_regno' AND class_name = '$class_name' AND class_group='$class_group' AND academic_session = '$academic_session' AND academic_term = '$academic_term' AND category='Assignment'";
        $ress = mysqli_query($conn,$sql1);
        $row1=$ress->fetch_assoc();
        $assignment=$row1['student_mark'];

        $sql2 = "SELECT student_mark FROM academic_scores WHERE student_regno ='$student_regno' AND class_name = '$class_name' AND class_group='$class_group' AND academic_session = '$academic_session' AND academic_term = '$academic_term' AND category = 'Test'";
        $res = mysqli_query($conn,$sql2);
        $row2=$res->fetch_assoc();
        $test=$row2['student_mark'];

        $sql3 = "SELECT student_mark FROM academic_scores WHERE student_regno ='$student_regno' AND class_name = '$class_name' AND class_group='$class_group' AND academic_session = '$academic_session' AND academic_term = '$academic_term' AND category = 'Examination'";
        $res = mysqli_query($conn,$sql3);
        $row3=$res->fetch_assoc();
        $exam=$row3['student_mark'];




       echo' <tr>
            <td>'.$subject.'</td>
            <td>'.$assignment.'</td>
            <td>'.$test.'</td>
            <td>'.$exam.'</td>

        </tr>';


       }
}

解决方案

这篇关于如何从具有不同条件的表中多次选择同一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！