我的代码有一个问题。我有两张桌子。第一个employee_id:
| |雇员id
1 23
第二个表叫做employee_times:
| Employee_id | Hours_dev | hours_pm |
| 1 | 2 | 3 |
| 1 | 3 | 4 |
| 2 | 3 | 3 |
我要做的是计算每个员工工作的总时间(hours_dev+hours_pm)。例如,employee_id 1已经工作了12小时
到目前为止,我已经尝试从第一个表中检索所有的employee_id,并使用for循环在SQL语句中遍历employee_times(参见下面的代码)。然而,该代码不起作用,因为它对employee_id和total_hours都打印0。我在本地主机服务器上使用MYSQL。
$sql = "SELECT employee_id FROM employee";$result = mysql_query($sql);While ($row = mysql_fetch_array){$employee_id = $row['employee_id'];}
$employee_id_length = sizeof($employee_id);
for($i = 0; $i < $employee_id_length; $i++)
{
$sql4 = "SELECT employee_id, hours_dev, hours_pm FROM employee_times WHERE employee_id= '$employee_id[$i]'";
$result = mysql_query($sql4);
while($info = mysql_fetch_array($result));
{
$employee_id = $info['employee_id'];
$hours_dev=$info['hours_dev'];
$hours_pm=$info['hours_pm'];
$total_hours = ($total_hours + $hours_dev + $hours_pm );
}
//print "$employee_id worked for $total_hours";
}
可以直接得到sum
select employee_id, sum(hours_dev)+ sum(hours_pm) as total
from employee_times WHERE employee_id= '1'
group by employee_id
请参考此Demo
这应该得到您需要的数据
SELECT
hours_dev,
hours_pm,
sum(hours_dev) + sum(hours_pm) as total_hours
FROM
employee_times
WHERE
employee_id = 123
GROUP BY
employee_id
看一下聚合函数:http://www.w3schools.com/sql/sql_functions.asphttp://www.w3schools.com/sql/sql_func_sum.asp
这个SQL查询应该比脚本更快地提取信息;
SELECT Employee_id, SUM(Hours_dev), SUM(Hours_pm), SUM(Hours_dev + Hours_pm)
FROM employee_times
GROUP BY Employee_id