A表(交易日历表)
A_DATE
2011-04-01
2011-04-06
2011-04-07
2011-04-08
2011-04-11F_GET_TRADE('2011-04-11') 获取指定日期的上一个交易日,如F_GET_TRADE_DATE('2011-04-11')返回2011-04-08B表交易明细表
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查询怎么实现?
A_DATE
2011-04-01
2011-04-06
2011-04-07
2011-04-08
2011-04-11F_GET_TRADE('2011-04-11') 获取指定日期的上一个交易日,如F_GET_TRADE_DATE('2011-04-11')返回2011-04-08B表交易明细表
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查询怎么实现?
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;
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>