在线求一SQL查询，谢谢
两个表：
表一：
Agency_code Agency_Name Amount COA_ID
----------- ------------- ------ --------
MVSIN Agency Name a 200.01 1950-000
MVSIN Agency Name b 300 1011-002
MVSIN Agency Name c 200.01 1111-002
MVSIN Agency Name d 200.01 1011-005
MVSIN Agency Name e 200.01 1111-099
MAXSGP Agency Name f 200.01 1211-003
MAXSGP Agency Name g 200.01 1111-005
MAXSGP Agency Name h 200.01 8150-000
MAXSGP Agency Name i 200.01 9160-101
MAXSGP Agency Name j 200.01 8380-001
MAXSGP Agency Name k 200.01 1540-000
表2：
Agency_Code Cagetory Group Range_Start Range_End
----------- ------------- ------ ------------- ---------
MAXSGP Net Billings: MEDIA 1011-100 1011-001
MAXSGP Cost of Billings: MEDIA 1111-100 1111-001
MAXSGP Service Fee: MEDIA 1211-100 1211-001
MAXSGP Commission: MEDIA 1211-100 1211-100
MAXSGP T&E Direct a/cs: OPERATING COSTS 1605-800 1600-400
MAXSGP Total Operating Costs: OPERATING COSTS 2601-000 1405-001
MVSIN Net Billings: MEDIA 1011-100 1011-001
MVSIN Cost of Billings: MEDIA 1111-100 1111-001
MVSIN Service Fee: MEDIA 1211-100 1211-001
MVSIN T&E Direct a/cs: OPERATING COSTS 1605-800 1600-200
MVSIN Total Operating Costs: OPERATING COSTS 2601-000 1405-200
要两个计算：
当agency_code=MVSIN时，所有group为Midia， 而且 coa_id 要在 range_start和range_end之间的， 所有的category为： Net Billings，Service Fee和Commission的Amout加起来。
也就是公式为： Net Billings(amount) + Service Fee(Amount) + Commission(Amount) 并且要符合我说的那些条件。
其它所有在表一中的coa_id如果不落在表二任何一个range_start和range_end之间的不用参加计算。
谢谢。
[解决办法]
http://topic.csdn.net/t/20061010/21/5073353.html
[解决办法]
select count(b1.amount)
from b1,b2
where b1.agency_code = b2.agency_code
and b1.agency_code = 'MVSIN '
and (b1.coa_id > b2.range_start and b1.coa_id < b2.range_end)
and b2.category in ( 'Net Billings '， 'Service Fee ', 'Commission ')
[解决办法]
select sum(amount)
from
(
select distinct a.*
from a,b
where a.agency_code=b.agency and group= 'Midia ' and a.agency_code= 'MVSIN '
and coa_id between range_start and range_end
and cagetory in( 'Net Billings ', 'Service Fee ', 'Commission ')
)aa
[解决办法]
select sum(a.Amount) from table1 a,table2 b where a.Agency_code=b.Agency_Code and b.Groups= 'MEDIA ' and b.Cagetory in(
'Net Billings ', 'Service Fee ', 'Commission的Amout ') and replace(a.COA_ID, '- ',0) between replace(Range_Start, '- ',0) and replace(Range_End, '- ',0)
[解决办法]
select sum(a.Amount) Amount from a,b
where a.Agency_Code = 'MVSIN ' and
b.Agency_Code = 'MVSIN ' and
b.Cagetory in ( 'Net Billings ' , 'Service Fee ' , 'Commission ') and
a.coa_id > = b.Range_Start and a.coa_id <= b.Range_End
