help，求一条sql话语._Sql Server

help，求一条sql语句..........
table A
{ id,value }

table B
{ id,aid,str }

表A与表B关联，通过aid 一对多的关系，求表B中Sum（str）大于等于表A中value的所有表A记录

eq：
A
1 50
2 30
3 40

B
1 1 10
2 1 20
3 2 30
4 3 20
5 3 20

得到的就是
A
2 30
3 40

求大虾指教。。。。

------解决方案--------------------

SQL code

SELECT A.*FROM AINNER JOIN (SELECT AID,SUM(STR) AS TOTALFROM BGROUP BY AID) B ON A.ID=B.AID AND B.TOTAL>=A.VALUE
------解决方案--------------------
SQL codeselect a.*from tba a outer apply (select sum(str) str from tbb where aid = a.id) bwhere a.str <= b.str
------解决方案--------------------
SQL codedeclare @A table (id int ,value  float)insert into @Aselect 1,50 union allselect 2,30 union allselect 3,40 declare @B table ( id int ,aid int ,[str]  float )insert into @Bselect 1,1,10 union allselect 2,1,20 union allselect 3,2,30 union allselect 4,3,20 union allselect 5,3,20 SELECT a.* FROM (select AID,SUM([str]) AS vALUE from @B GROUP BY AID)B LEFT JOIN @A A on b.AID = A.id where b.vALUE >= a.value