我对要问的标题感到困惑。当我希望看到学生报告每个主题的价值时,我正在做这个工作。

下面是我关于数据库的主题表。

+-----------+----------------------+----------------------+
| 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代码中进行处理。

10-07 14:28