查询语句中根据日历计算结果

[aibeyond2003]

A表(交易日历表)
A_DATE
2011-04-01
2011-04-06
2011-04-07
2011-04-08
2011-04-11

F_GET_TRADE('2011-04-11') 获取指定日期的上一个交易日，如F_GET_TRADE_DATE('2011-04-11')返回2011-04-08

B表交易明细表
b_date b_type b_port b_count
2011-04-01 B 001 1000
2011-04-02 B 001 2000
2011-04-03 B 001 1500
2011-04-06 B 001 1000
2011-04-07 B 001 1000
2011-04-08 B 001 2000
2011-04-09 B 001 1000
2011-04-11 B 001 1000

需求：将B表中的日期与A表比较，如果B表中的日期在A表中不存在就将其结果汇总到此记录的下一个交日日中。
B表中的查询结果如下：
b_date b_type b_port b_count
2011-04-01 B 001 1000
2011-04-06 B 001 4500
2011-04-07 B 001 1000
2011-04-08 B 001 2000
2011-04-11 B 001 2000

这样的SQL查询怎么实现？

[tangren]

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;

[tangren]

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> 

[horizonlyhw]

[Quote=引用 1 楼 tangren 的回复:]
SQL code

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 ……
[/Quote]

真牛逼！ 学习！