17,377
社区成员
发帖
与我相关
我的任务
分享
SELECT T1.ID,
T1.NAME,
T1.WORK_TIME,
(SELECT SUM(WORK_HOUSE)
FROM (SELECT WORK_HOUSE,
ROW_NUMBER() OVER(ORDER BY ON_TIME DESC) RN
FROM PRODUCT_201402 T2
WHERE TO_CHAR(T2.ON_TIME, 'yyyymmdd') <=
TO_CHAR(T1.WORK_TIME, 'yyyymmdd')
AND T1.ID = T2.ID) T
WHERE T.RN <= 2)
FROM PRODUCT T1
select id, name, work_time, last_work_house
from product c,
(select a.product_id, sum(a.work_house) as last_work_house
from product_morth a, product b
where to_char(a.on_time, 'yyyymmdd') between
to_char(b.work_time - 1, 'yyyymmdd') and
to_char(b.work_time, 'yyyymmdd')
and a.product_id = b.id
group by product_id) d
where c.id = d.product_id(+);