如何在MySQL中动态地将行转置为列

deyfvvtc 于 2023-10-15 发布在 Mysql

关注(0)|答案(2)|浏览(130)

我正在建设一个关于如何显示一个学校大报迷你项目，我已经尝试了所有我知道最好的，但没有解决方案，请我需要你的帮助。我有三个表学生，分数，主题。
这就是我想要的显示器的样子。
| 注册号|学生姓名|数学|英语|总|平均|
| --|--|--|--|--|--|
| TGD102|本杰明·金| 60 | 55 | 115 |五十七点五|
| TGD103|大卫马修| 50 | 45 | 95 |47.5|
| TGD104|玛丽·詹姆斯| 45 | 50 | 95 |47.5|
| TGD105|所罗门大卫| 70 | 45 | 115 |五十七点五|
我已经尽了最大的努力，但只能在下面这样显示它。
| 注册号|学生姓名|主题|标志|
| --|--|--|--|
| TGD102|本杰明·金|数学| 50 |
| TGD103|大卫马修|数学| 45 |
| TGD104|玛丽·詹姆斯|数学| 35 |
| TGD105|所罗门大卫|数学| 55 |
| TGD102|本杰明·金|英语| 45 |
| TGD103|大卫马修|英语| 35 |
| TGD104|玛丽·詹姆斯|英语| 40 |
| TGD105|所罗门大卫|英语| 50 |
这是我用来显示上面表格的代码。

<table id="myTable" class="table table-bordered table-striped">
    <thead>
        <th>Reg</th>
        <th>Name</th>
        <th>Subject</th>
        <th>marks</th>
    </thead>
    <tbody>
    <?php
        include('db/conn.php');

        $query=mysqli_query($conn,"select * from student,score,subject 
                where student.stu_id=score.scostu_id
                AND score.scosbj_id=subject.subject_id ");
                
        while($row2=mysqli_fetch_array($query)){
    ?>
        <tr>
            <td align="center"><?php echo $row2['stu_id']; ?></td>
            <td><span style="text-transform:uppercase;"><?php echo $row2['name']; ?></span></td>
            <td><span style="text-transform:uppercase;"><?php echo $row2['subject_title']; ?></span></td>
            <td><span style="text-transform:uppercase;"><?php echo $row2['exam']; ?></span></td>
        </tr>
    <?php
        }
    ?>
    </tbody>
</table>

我怎么能修改上述代码给我给予一些这样的东西。
| 注册号|学生姓名|数学|英语|总|平均|
| --|--|--|--|--|--|
| TGD102|本杰明·金| 45 | 50 | 95 |47.5|
| TGD103|大卫马修| 50 | 45 | 95 |47.5|
| TGD104|玛丽·詹姆斯| 60 | 30 | 90 | 45 |
| TGD105|所罗门·德维德| 35 | 50 | 85 |四十二点五|

mysql

来源：https://stackoverflow.com/questions/77213593/how-to-transpose-rows-into-columns-dynamically-in-mysql

2条答案

按热度按时间

nmpmafwu1#

这段代码没有优化，但会做你需要的工作。

<?php

$subjects = array();
$query = mysqli_query($conn,"select * from subject");
while($row = mysqli_fetch_array($query)){
    $subjects[$row['subject_id']] = $row['subject_title'];
}

?>

<table id="myTable" class="table table-bordered table-striped">
<thead>
<th>Reg</th>
<th>Name</th>
<?php foreach($subjects as $sbj) { ?>
<th><?= $sbj; ?></th>
<?php } ?>
<th>Total</th>
<th>Average</th>
</thead>
<tbody>
<?php
$query1 = mysqli_query($conn,"select * from student");
while($row1 = mysqli_fetch_array($query1)){
    
    $scores = array();
    $query2 = mysqli_query($conn, "select * from score where scostu_id = " . $row1['stu_id']);
    while($row2 = mysqli_fetch_array($query2)){
        $scores[$row2['scosbj_id']] = $row2['score_value'];
    }
    
    $total = 0;
    $count = 0;
?>
<tr>
<td align="center"><?= $row2['stu_id']; ?></td>
<td><?= $row2['name']; ?></td>
<?php
foreach($subjects as $sbj_id => $sbj_title) {
    $total += $scores[$sbj_id];
    $count++;
?>
<td><span><?= $scores[$sbj_id]; ?></span></td>
<?php } ?>
<td><?= intval($total); ?></td>
<td><?= (($total > 0 && $count > 0)?(round($total / $count)):0); ?></td>
</tr>
<?php } ?>

赞(0）回复(0）举报 2023-10-15

ozxc1zmp2#

使用以下查询以所需格式获取数据

select stu_id, name, 
SUM(CASE WHEN subject_title = 'Mathematics' THEN exam ELSE 0 END) as Mathematics, 
SUM(CASE WHEN subject_title = 'English' THEN exam ELSE 0 END) as English, 
sum(exam) as Total, round(avg(exam),1) as Average from (select stu_id, name, subject_title, 
exam from student inner join (select scostu_id, subject_title, exam from score left join 
subject on score.scosbj_id = subject.subject_id) as t1 on student.stu_id = t1.scostu_id) 
as t2 GROUP BY t2.stu_id

它将给出类似于下面的给予输出

相应地修改您的HTML代码以容纳额外的列。享受.

赞(0）回复(0）举报 2023-10-15

我来回答

如何在MySQL中动态地将行转置为列

2条答案

相关问题

热门标签

最新问答