问一个sql的问题

Question

表a：1 aaa 1 3 //表示aaa是表b1-3的和
2 bbb 2 4
3 ccc 2 6表b：1 200
2 300
3 400
4 100
5 150
6 120怎么能写sql得到：
aaa 900
bbb 800
ccc 1070
先谢谢各位！

Answer 1

SQL> select * from a;
 
                                   COL1 COL2                                          COL3                                    COL4
--------------------------------------- ---------- --------------------------------------- ---------------------------------------
                                      1 aaa                                              1                                       3
                                      2 bbb                                              2                                       4
                                      3 ccc                                              2                                       6
 
SQL> select * from b;
 
                                   COL1                                    COL2
--------------------------------------- ---------------------------------------
                                      1                                     200
                                      2                                     300
                                      3                                     400
                                      4                                     100
                                      5                                     150
                                      6                                     120
 
6 rows selected
 
SQL> 
SQL> select a.col2,sum(b.col2)
  2  from   a,b
  3  where  b.col1 between a.col3 and a.col4
  4  group by a.col1,a.col2;
 
COL2       SUM(B.COL2)
---------- -----------
aaa                900
bbb                800
ccc               1070

Answer 2

select a,sum(money) from a,b where id >=s and id <= e group by a

Answer 3

with a as(
select 'aaa' a,1 s,3 e from dual
union all
select 'bbb' a,2 s,4 e from dual
union all
select 'ccc' a,2 s,6 e from dual
)
,
b as(
select 1 id,200 money from dual
union all
select 2 id,300 money from dual
union all
select 3 id,400 money from dual
union all
select 4 id,100 money from dual
union all
select 5 id,150 money from dual
union all
select 6 id,120 money from dual
)
select a,sum(money) from a,b where id >=s and id <= e group by a

Answer 4

SQL> select * from a;
 
                                   COL1 COL2                                          COL3                                    COL4
--------------------------------------- ---------- --------------------------------------- ---------------------------------------
                                      1 aaa                                              1                                       3
                                      2 bbb                                              2                                       4
                                      3 ccc                                              2                                       6
 
SQL> select * from b;
 
                                   COL1                                    COL2
--------------------------------------- ---------------------------------------
                                      1                                     200
                                      2                                     300
                                      3                                     400
                                      4                                     100
                                      5                                     150
                                      6                                     120
select a.col2, nvl(b1.col2, 0)+nvl(b2.col2,0) from a, b b1, b b2 where a.col3=b1.col1(+) and a.col4=b2.col1(+)

Answer 5

刚才题意理解错了。同意这个
with a as(
select 'aaa' a,1 s,3 e from dual
union all
select 'bbb' a,2 s,4 e from dual
union all
select 'ccc' a,2 s,6 e from dual
)
,
b as(
select 1 id,200 money from dual
union all
select 2 id,300 money from dual
union all
select 3 id,400 money from dual
union all
select 4 id,100 money from dual
union all
select 5 id,150 money from dual
union all
select 6 id,120 money from dual
) 
select a.a, sum(b.money) from a,b where a.s<=b.id and a.e>=b.id group by a.a;