在将它们组合在一起之后，是否可以将结果组合在一起? [英] Is it possible to group results together after, well, grouping them together?

问题描述

表结构1(clockins):日期 |时间 |超时 |工作代码 |工作率|员工编号

Table Structure 1 (clockins): date | timein | timeout | jobcode | jobrate | employeeid

表结构 2 (jobdesc):工作代码 |职位描述

Table Structure 2 (jobdesc): jobcode | jobdescription

所以我有一个查询:

SELECT
    jd.jobdescription,
    ci.employeeid,
    ci.jobcode,
    SUM(timestampdiff(minute,ci.timein,ci.timeout) / 60) * ci.jobrate
FROM clockins ci
RIGHT JOIN jobdesc jd ON ci.jobcode = jd.jobcode
WHERE ci.date BETWEEN '0000-00-00' AND '0000-00-00'
AND jobrate < 100 /* need this to distinguish salaried employees */ 
GROUP BY `jobcode` , `employeeid`

这会输出如下数据:

示例输出

我相信一定有更好的方法来实现这一点，但这种方法将其分开，这很重要，因为每个工作角色可能有不同的员工，工作率不同.所以我需要的是一种在 mysql 或 php 中达到这一点后缺乏更好的术语重新组合"它们的方法，或者如果您对如何更好地进行查询也有建议，那只是我都可以.我试图找到这个，但我无法找到适合这种情况的答案.预先感谢您的帮助.

I am sure there must be a better way to accomplish this, but this way separates it out which is important because each job role might have different employees with different job rates. So what I need is a way to for lack of a better term "re-group" them after getting to this point in either mysql or php OR if you have suggestion on how to just do the query better too, that'd be just fine with me. I tried to find this but I couldn't quite come across an answer that would fit this situation. Thanks in advance for the help.

推荐答案

我最终得到了同事的帮助来解决这个问题.以防万一其他人正在寻找类似的东西，这是在 PHP 中的做法:

I ended up getting colleagues help solving this. Just in case anyone else was looking for something similar here's how it's done in PHP:

<?php
$laborTotals = array();
$totalLaborDollars= 0;
$laborTotals[$laborAnalysis['jobcode']]['jobcode'] = $laborAnalysis['jobrole'];
    $laborTotals[$laborAnalysis['jobcode']]['jobdescription'] = $laborAnalysis['jobdescription'];
    $laborTotals[$laborAnalysis['jobcode']]['totalsalary'] += $laborAnalysis['totalsalary'];

}

foreach ($laborTotals as $labor) { 
    $html .= '  <tr>';
    $html .= '    <td class="gridData">' . $labor['jobcode'] . '</td>';
    $html .= '    <td class="gridData">' . $labor['jobdescription'] . '</td>';
    $html .= '    <td class="gridData txtLeft"><b>$</b>' . number_format($labor['totalsalary'], 2, '.', ',') . '</td>';
    $totalLaborDollars+=$labor['totalsalary'];
}

?>

这篇关于在将它们组合在一起之后，是否可以将结果组合在一起?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！