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(+);
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(+);
徐州.net 程序员qq群:292143448