我有一个查询,该查询返回某人上班的时间。但是,这给了我每个人多个记录。有没有一种方法可以将时间加在一起,这样我就有了每个员工的总时间。
查询是:
select
employee.first_name,
employee.last_name,
to_number( to_char(to_date('1','J') +
(time_sheet.finish_date_time - time_sheet.start_date_time), 'J') - 1) days,
to_char(to_date('00:00:00','HH24:MI:SS') +
(time_sheet.finish_date_time - time_sheet.start_date_time), 'HH24:MI:SS') time
from
employee
inner join
employee_case on employee.employee_id = employee_case.employee
inner join
time_sheet on time_sheet.employee_case = employee_case.employee_case_id
where
employee_case.case = 1;
当前输出为:
但我想将史蒂夫·贝德(Steve Baid)的值合并为1。
有任何想法吗?
我认为您需要将其作为嵌套查询来执行:
select
first_name,
last_name,
to_number( to_char(to_date('1','J') + (duration), 'J') - 1) days,
to_char(to_date('00:00:00','HH24:MI:SS') + (duration), 'HH24:MI:SS') time
from (
select
employee.first_name first_name,
employee.last_name last_name,
time_sheet_sum.duration duration
from
employee
inner join
(
select
distinct employee_case.employee_id employee_id,
sum(time_sheet.finish_date_time - time_sheet.start_date_time) duration
from
employee_case
inner join
time_sheet on time_sheet.employee_case = employee_case.employee_case_id
where
employee_case.case = 1
group by
employee_case.employee_id
) time_sheet_sum on employee.employee_id = time_sheet_sum.employee
);
注意:我无法测试或验证此代码。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句