高手们,帮忙看下:
怎样求overtime_total -leavetime_total的和?select empno, empname, sum(overtime_total -leavetime_total)over(partition by empno)
from (
select e.empno, e.empname,
f_cokstat_equal_time(a.starttime, a.endtime) overtime_total
from t_employeeinfo e, t_overtime a
where e.empno = a.empno(+) union all select e.empno, e.empname,
f_cokstat_record_one(b.starttime, b.endtime) leavetime_total
from t_employeeinfo e, t_leave b
where trunc(to_date(b.starttime,'yyyy-mm-dd hh24')) <= to_date(b.endtime,'yyyy-mm-dd')
and e.empno = b.empno(+)
);
怎样求overtime_total -leavetime_total的和?select empno, empname, sum(overtime_total -leavetime_total)over(partition by empno)
from (
select e.empno, e.empname,
f_cokstat_equal_time(a.starttime, a.endtime) overtime_total
from t_employeeinfo e, t_overtime a
where e.empno = a.empno(+) union all select e.empno, e.empname,
f_cokstat_record_one(b.starttime, b.endtime) leavetime_total
from t_employeeinfo e, t_leave b
where trunc(to_date(b.starttime,'yyyy-mm-dd hh24')) <= to_date(b.endtime,'yyyy-mm-dd')
and e.empno = b.empno(+)
);
sum(leavetime_total)over(partition by empno)
1.sum(overtime_total)over(partition by empno)-sum(leavetime_total)over(partition by empno)2.sum(overtime_total-leavetime_total)over(partition by empno)3.sum(overtime_total-leavetime_total)+group by
情况1:报leavetime_total标识符无效
情况2.3:求和为0我也不知道怎么搞的?搞了几天了都是这样!
求高手们帮忙解决下!
第一个别名是overtime_total
第二段别名是leavetime_total 这样出来的是同一列,列名为overtime_total,第二列的别名是无效的
你应该把第二段的值直接赋为负值
然后在外层
sum(overtime_total )over(partition by empno)
t_employeeinfo e, t_overtime a ,t_leave b
from (
select e.empno, e.empname,
f_cokstat_equal_time(a.starttime, a.endtime) overtime_total
from t_employeeinfo e, t_overtime a
where e.empno = a.empno(+)union allselect e.empno, e.empname,
-f_cokstat_record_one(b.starttime, b.endtime) overtime_total
from t_employeeinfo e, t_leave b
where trunc(to_date(b.starttime,'yyyy-mm-dd hh24')) <= to_date(b.endtime,'yyyy-mm-dd')
and e.empno = b.empno(+)
);