17,382
社区成员




SQL> select * from a;
A_DATE
-----------
2011-4-1
2011-4-6
2011-4-7
2011-4-8
2011-4-11
SQL> select * from b;
B_DATE B_TYPE B_PROT B_COUNT
----------- ------ ------ ----------
2011-4-1 B 001 1000
2011-4-2 B 001 2000
2011-4-3 B 001 1500
2011-4-6 B 001 1000
2011-4-7 B 001 1000
2011-4-8 B 001 2000
2011-4-9 B 001 1000
2011-4-11 B 001 1000
8 rows selected
SQL>
SQL> SELECT c.a_date, b.b_type, b.b_prot, SUM(b.b_count)
2 FROM b,
3 (SELECT a.a_date,
4 lag(a.a_date, 1, DATE '1900-01-01') over(ORDER BY a.a_date) p_date
5 FROM a) c
6 WHERE b.b_date > c.p_date
7 AND b.b_date <= c.a_date
8 GROUP BY c.a_date, b.b_type, b.b_prot
9 ORDER BY c.a_date;
A_DATE B_TYPE B_PROT SUM(B.B_COUNT)
----------- ------ ------ --------------
2011-4-1 B 001 1000
2011-4-6 B 001 4500
2011-4-7 B 001 1000
2011-4-8 B 001 2000
2011-4-11 B 001 2000
SQL>
SELECT c.a_date, b.b_type, b.b_prot, SUM(b.b_count)
FROM b,
(SELECT a.a_date,
lag(a.a_date, 1, DATE '1900-01-01') over(ORDER BY a.a_date) p_date
FROM a) c
WHERE b.b_date > c.p_date
AND b.b_date <= c.a_date
GROUP BY c.a_date, b.b_type, b.b_prot
ORDER BY c.a_date;