php - 连接两个表，并将某些行数据表示为列

Question

我有两个数据表，一个代表员工数据，另一个代表员工工作时间。不幸的是，我收到行格式的员工工时数据，我需要将其表示在员工数据旁边的列中...这让我很头疼...请参见下文...

员工工作时间表:

ID--------Paycode-------------Hours----workdate

089999 01 Ordinary --------4.00 -----2013-09-16

089999 02 Overtime 1.5 ---2.00 -----2013-09-16

089999 03 Overtime 2.0 ---0.50 -----2013-09-16

083131 01 Ordinary --------7.60 -----2013-09-16

083131 02 Overtime 1.5--- 0.43----- 2013-09-16

员工数据表:

ID ------ Surname -- firstname --- salarygrade
-------------------baserate --- otherrate

089999 SMITH ----- JOHN -------- XXX TWU EBA Perm Gr6 -- 23.8508 ---- 0.0000

<小时/>

我想要一个查询来产生如下结果，我已经尽我所能，但我无法让它工作...我知道我可能需要一些 php 编程来产生 $ 值，但它是吗？也可以通过 SQL 来做到这一点吗？非常感谢所有帮助...

ID ------ Surname -- firstname --- salarygrade
------------------- 01 Hrs--- 01 $ ---- 02 Hrs---- 02 $ ---03 Hrs -03 $

089999 SMITH ----- JOHN -------- XXX TWU EBA Perm Gr6 ---4.00----95.4032 -- 2.00 ----71.5524--0.50--23.8508

某些具有 ID 的员工的数据可能不存在，并且在上面的某些列中将用 0.00 表示...

这是我尝试过的:

    <?php
        $result1=mysql_query("SELECT `ID Number`, Surname, `First Name`, `Base Rate`, `Other Rate`, `Salary Code Description`, workdate, employeehours
                                FROM payroll.employeedatanew INNER JOIN payroll.employeehours ON employeedatanew.`ID Number` = employeehours.employeeid
                                GROUP BY Surname ORDER BY Surname Asc
                            ");

        $result2=mysql_query("  SELECT `ID Number`, paycode, workdate, employeehours
                                FROM payroll.employeedatanew INNER JOIN payroll.employeehours ON employeedatanew.`ID Number` = employeehours.employeeid
                                ORDER BY Surname Asc
                            ");


        while($show1=mysql_fetch_array($result1)){

        echo("<tr><td>".$show1['ID Number']."</td><td>".$show1['Surname']."</td><td>".$show1['First Name']."</td><td>".substr($show1['Salary Code Description'],11)."</td></tr>");

        }
        ?>
        </table>

        <table>
        <tr>

        <td>1.0x Hrs</td>
        <td>1.0x $</td>
        <!--<td>1.5x Hrs</td>
        <td>1.5x $</td>
        <td>2.0x Hrs</td>
        <td>2.0x $</td>
        <td>Crib Hrs</td>
        <td>Crib $</td>
        <td>Meal Hrs</td>
        <td>Meal $</td> -->

        </tr>
        <?php

        $resultset = array();
        while ($row = mysql_fetch_assoc($result3)) { $resultset[] = $row;  }

            while($show2=mysql_fetch_array($result2)){

            if ($show2['paycode'] == "01 Ordinary" && $show2['ID Number'] == $resultset['ID Number']) {
            echo ("<tr><td>".$show2['employeehours']."</td>");
            $normhourspay = ($show2['employeehours'] * $show3['Base Rate']);
            echo ("<td>".$normhourspay."</td></tr>");
            }           //else {echo("<tr><td>0</td><td>0</td></tr>");}}

还有这个:

    <table>
    <tr class="tabletitles">
        <td>Employee ID</td>
        <td>Surname</td>
        <td>First Name</td>
        <td>Pay Grade</td>
        <td>1.0x Hrs</td>
        <td>1.0x $</td>
        <!--<td>1.5x Hrs</td>
        <td>1.5x $</td>
        <td>2.0x Hrs</td>
        <td>2.0x $</td>
        <td>Crib Hrs</td>
        <td>Crib $</td>
        <td>Meal Hrs</td>
        <td>Meal $</td> -->

    </tr>

    <?php
        $result1=mysql_query("SELECT `ID Number`, Surname, `First Name`, `Base Rate`, `Other Rate`, `Salary Code Description`, workdate, employeehours
                                FROM payroll.employeedatanew INNER JOIN payroll.employeehours ON employeedatanew.`ID Number` = employeehours.employeeid
                                GROUP BY Surname ORDER BY Surname Asc
                            ");

        $result2=mysql_query("  SELECT `ID Number`, paycode, workdate, employeehours
                                FROM payroll.employeedatanew INNER JOIN payroll.employeehours ON employeedatanew.`ID Number` = employeehours.employeeid
                                ORDER BY Surname Asc
                            ");


        while($show1=mysql_fetch_array($result1)){

        echo("<tr><td>".$show1['ID Number']."</td><td>".$show1['Surname']."</td><td>".$show1['First Name']."</td><td>".substr($show1['Salary Code Description'],11)."</td></tr>");

        }
        echo ("</table>");

            while($show2=mysql_fetch_array($result2)){

            if ($show2['paycode'] == "01 Ordinary" && $show2['ID Number'] == $show1['ID Number']) {
            echo ("<td>".$show1['employeehours']."</td>");
            $normhourspay = ($show1['employeehours'] * $show1['Base Rate']);
            echo ("<td>".$normhourspay."</td></tr>");
            }           else {echo("<td>0</td><td>0</td>");}
                }   

请帮忙!

Answer 1

SELECT `ID Number`, Surname, `First Name`, `Salary Code Description`,
                                `Base Rate`, `Other Rate`,
                                MAX((case when employeehours.paycode = '01 Ordinary' then (employeehours.employeehours) end)) AS `1.0 Hours`,
                                MAX((case when employeehours.paycode = '02 Overtime 1.5' then (employeehours.employeehours) end)) AS `1.5 Hours`,
                                MAX((case when employeehours.paycode = '03 Overtime 2.0' then (employeehours.employeehours) end)) AS `2.0 Hours`,
                                MAX((case when employeehours.paycode = '78 Crib' then (employeehours.employeehours) end)) AS `Crib`,
                                MAX((case when employeehours.paycode = 'CZ Meal Allowance PS' then (employeehours.employeehours) end)) AS `Meal Allowance`,
                                MAX((case when employeehours.paycode = '86Y Sick with Cert' then (employeehours.employeehours) end)) AS `Sick with Cert`,
                                MAX((case when employeehours.paycode = '86N Sick without Cert' then (employeehours.employeehours) end)) AS `Sick without Cert`,
                                MAX((case when employeehours.paycode = '87 Sick without Pay' then (employeehours.employeehours) end)) AS `Sick without Pay`,
                                MAX((case when employeehours.paycode = '83 Annual Leave' then (employeehours.employeehours) end)) AS `Annual Leave`,
                                MAX((case when employeehours.paycode = '95 RDO Taken' then (employeehours.employeehours) end)) AS `RDO Taken`,
                                MAX((case when employeehours.paycode = '85 LSL' then (employeehours.employeehours) end)) AS `LSL`,
                                MAX((case when employeehours.paycode = '61 Shift 17.5' then (employeehours.employeehours) end)) AS `Shift 17.5`
                                FROM payroll.employeedatanew INNER JOIN payroll.employeehours ON employeedatanew.`ID Number` = employeehours.employeeid
                                Group By Surname
                                ORDER BY Surname asc, `1.0 Hours` desc, `1.5 Hours` desc, `2.0 Hours` desc

这成功了，感谢大家的观看:)