我对要问的标题感到困惑。当我希望看到学生报告每个主题的价值时，我正在做这个工作。
下面是我关于数据库的主题表。
+-----------+----------------------+----------------------+
| SubjectID | SubjectName | ThemeName |
+-----------+----------------------+----------------------+
| 1 | Subject1 | Myself |
| 2 | Subject1 | My Hobbies |
| 3 | Subject1 | My Activity |
| 4 | Subject1 | My Family |
| 5 | Subject2 | My Experience |
| 6 | Subject2 | Environment |
| 7 | Subject2 | Things |
| 8 | Subject2 | Nature Incident |
+-----------+----------------------+----------------------+
这是每个主题的表值。
+-----------+-----------+-----------+------------+
| ValueID | StudentID | SubjectID | FinalScore |
+-----------+-----------+-----------+------------+
| 1 | 112 | 1 | 80 |
| 2 | 112 | 2 | 90 |
| 3 | 112 | 3 | 50 |
| 4 | 112 | 4 | 70 |
| 5 | 113 | 1 | 60 |
| 6 | 113 | 2 | 40 |
| 7 | 113 | 3 | 50 |
| 8 | 113 | 4 | 90 |
+-----------+-----------+-----------+------------+
我想像下面的表格一样显示在我的html上
+----+-----------+------------+------------+------------+------------+
| No | StudentID | ThemeName1 | ThemeName2 | ThemeName3 | ThemeName3 |
+----+-----------+------------+------------+------------+------------+
| 1 | 112 | 80 | 90 | 50 | 70 |
| 2 | 113 | 60 | 40 | 50 | 90 |
+----+-----------+------------+------------+------------+------------+
我现在正在使用的这张桌子。忽略空的东西，我会很快将其删除。
<table cellspacing="0" border="0" class="table display table-bordered" id="table1">
<thead>
<tr>
<th width="1%"><center>No</th>
<th width="10%"><center>Nama Siswa</th>
<th width="12%"><center>Nama Mapel</th>
<th width="25%"><center>Diri Sendiri</th>
<th width="5%"><center>Kegemaranku</th>
<th width="5%"><center>Kegiatanku</th>
<th width="5%"><center>Keluargaku</th>
<th width="5%"><center>TG4</th>
<th width="1%"><center>Aksi</th>
</tr>
</thead>
<tbody>
<?php
$no = 1;
$query = mysql_query("SELECT NISN, MAX(CASE WHEN KodeMapel = 29 THEN NilaiAkhir END) AS Diri Sendiri, MAX(CASE WHEN KodeMapel = 30 THEN NilaiAkhir END) AS Kegemaranku, MAX(CASE WHEN KodeMapel = 31 THEN NilaiAkhir END) AS Kegiatanku, MAX(CASE WHEN KodeMapel = 32 THEN NilaiAkhir END) AS Keluargaku FROM pengetahuan
GROUP BY NISN");
while ($pengetahuan = mysql_fetch_array($query))
{
$kode = $pengetahuan['KodeP'];
$nisn = $pengetahuan['NISN'];
$namasiswa = $pengetahuan['NamaSiswa'];
$namatema = $pengetahuan['NamaTema'];
$namamapel = $pengetahuan['NamaMapel'];
$na = $pengetahuan['NilaiAkhir'];
?>
<tr>
<td>
<center>
<?php echo $no++; ?>
</center>
</td>
<td>
<center><?php echo $namasiswa; ?>
</td>
<td>
<center><?php echo $namamapel; ?>
</td>
<td>
<?php echo $na; ?>
</td>
<td>
<center>
</td>
<td>
<center>
</td>
<td>
<center>
<?php
?>
</td>
<td>
<center>
<?php
?>
</td>
<td>
<center>
<?php
?>
</td>
<td>
<center>
<?php
?>
</td>
<td>
<center>
<?php
?>
</td>
<td>
<center>
<button data-id="<?php echo "$kode"; ?>" type="button" class="btn btn-info btn-sm" data-toggle="modal" data-target="#myModal12"><i class="fa fa-pencil fa-fw"></i> Tambah Nilai</button>
<a href="Pengetahuan/Tema4/action.php?KodeP= <?php echo $$kode; ?>" class="btn btn-danger btn-sm" id="submit" type="submit"/>
<i class="fa fa-trash fa-fw"></i> Hapus </a>
</center>
</td>
</tr>
<?php
}
?>
</tbody>
</table>
有可能吗？
谢谢
最佳答案
您可以使用以下数据透视查询来获取所需的常规表：
SELECT
StudentID,
MAX(CASE WHEN SubjectID = 1 THEN FinalScore END) AS ThemeName1,
MAX(CASE WHEN SubjectID = 2 THEN FinalScore END) AS ThemeName2,
MAX(CASE WHEN SubjectID = 3 THEN FinalScore END) AS ThemeName3,
MAX(CASE WHEN SubjectID = 4 THEN FinalScore END) AS ThemeName4
FROM yourTable
GROUP BY StudentID
要生成
No列，可以在迭代结果集时在PHP代码中进行处理。