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 40B
1 1 10
2 1 20
3 2 30
4 3 20
5 3 20得到的就是
A
2 30
3 40求大虾指教。
{ id,value }table B
{ id,aid,str }表A与表B关联,通过aid 一对多的关系,求表B中Sum(str)大于等于表A中value的所有表A记录eq:
A
1 50
2 30
3 40B
1 1 10
2 1 20
3 2 30
4 3 20
5 3 20得到的就是
A
2 30
3 40求大虾指教。
FROM A
INNER JOIN (
SELECT AID,SUM(STR) AS TOTAL
FROM B
GROUP BY AID
) B ON A.ID=B.AID AND B.TOTAL>=A.VALUE
select a.*
from tba a outer apply (select sum(str) str from tbb where aid = a.id) b
where a.str <= b.str
(id int ,value float)insert into @A
select 1,50 union all
select 2,30 union all
select 3,40 declare @B table
( id int ,aid int ,[str] float )
insert into @B
select 1,1,10 union all
select 2,1,20 union all
select 3,2,30 union all
select 4,3,20 union all
select 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