php - 在一行中显示多行

Question

我正在开发一个页面，我必须在其中显示员工的所有工作以及工作编号和每项工作中每种类型的总时数

我可以显示它，但是，我的问题是

当雇主在同一份工作上工作两次时，它也会显示同一份工作两次

我有这个:

JOB      | H1     | H2      | H3      | H4      | H5      | H6

O-2019-1 |   2.00 |    2.00 |    0.00 |    0.00 |    0.00 |    1.50        
O-2019-1 |   1.50 |    0.50 |    0.00 |    0.00 |    1.50 |    0.00

我想要这个:

JOB      | H1    | H2       | H3      | H4      | H5      | H6

O-2019-1 |  3.50 |     2.50 |    0.00 |    0.00 |    1.50 |    1.50

PHP 代码:

<?php 
    $stmt = $link -> prepare("SELECT * FROM rostosativos_funcionarios WHERE contribuinte = ?;");
    $stmt -> bind_param('s',
            $_GET['id']);
    $stmt -> execute();
    $resultado = $stmt -> get_result();
    $r = $resultado -> fetch_assoc();
    $id_funcionario = $r['id_empregado'];
    $stmt -> close();


    $sql = $link -> prepare("SELECT * FROM rostosativos_trabalhos WHERE id_funcionario = ?;");
    $sql -> bind_param('s',
            $id_funcionario);
    $sql -> execute();
    $resultado = $sql -> get_result();
    $sql -> close();

?>

HTML 代码:

<?php while($r = $resultado -> fetch_assoc()){ ?>
<section class="panel">
    <div class="panel-body">
        <div class="form-group">
            <div class="col-sm-8">
                <div class="row">
                    <label class="col-sm-2 control-label">Obra: </label>
                    <div class="col-sm-2">
                        <input type="text" value="<?php echo $r['id_obra']; ?>" class="form-control" disabled>
                    </div>
                </div>
            </div>
        </div>
        <div class="form-group">
            <div class="col-sm-8">
                <div class="row">
                    <label class="col-sm-2 control-label">Hora 1: </label>
                    <div class="col-sm-2">
                        <input type="text" value="<?php echo $r['hora_a']; ?>h" class="form-control" disabled>
                    </div>

                    <label class="col-sm-2 control-label">Hora 2: </label>
                    <div class="col-sm-2">
                        <input type="text" value="<?php echo $r['hora_b']; ?>h" class="form-control" disabled>
                    </div>

                    <label class="col-sm-2 control-label">Hora 3: </label>
                    <div class="col-sm-2">
                        <input type="text" value="<?php echo $r['hora_c']; ?>h" class="form-control" disabled>
                    </div>
                </div>
            </div>
        </div>
    </div>
</section>

Answer 1

您想对第一列使用 group by 并在小时内使用 sum group 函数

Select job, sum(h1) h1, sum(h2) h2, ....
From ....
Where ....
Group by job