高手请进,复杂SQL求教!
假定有两张表:
TABLE   A
RowCode                           SQLString
-----------------------
1000                                 acct   Between   72110000   and   72112999  
1100                                 acct   Between   72120000   and   72122999
1200                                 acct   Between   72130000   and   72132999
....
数据有将近200条记录,SQLString中是对应TABLE   B中的条件语句

TABLE   B
Acct                               Amt
-----------------------------------------------------
72110000                       100
72111800                       200
72111900                       300
72120000                       400
72128000                       500
72130000                       600

如何获得如下结果(不要用游标的方式):
RowCode                         Amt
---------------------------
1000                               600           (注释:100+200+300)
1100                               900           (注释:400+500)
1200                               600           (注释:600)
...

------解决方案--------------------
如果位数定长,不变.

select rowcode , sum(amt) as amt from
(
select b.acct , b.amt , a.rowcode
from a , b
where b.acct > = substr(a.SQLString , 14 , 8) and b.acct <= right(a.SQLstring , 8)
) t
group by rowcode
------解决方案--------------------
declare @A TABLE(RowCode int,SQLString varchar(40))
insert into @A select 1000, 'acct Between 72110000 and 72112999 '
insert into @A select 1100, 'acct Between 72120000 and 72122999 '
insert into @A select 1200, 'acct Between 72130000 and 72132999 '

declare @B TABLE(Acct int,Amt int)
insert into @B select 72110000,100
insert into @B select 72111800,200
insert into @B select 72111900,300
insert into @B select 72120000,400
insert into @B select 72128000,500
insert into @B select 72130000,600


select
a.RowCode,sum(b.Amt) as Amt
from
@A a,@B b
where
b.Acct between
(substring(SQLString,13,charindex( ' and ',SQLString)-13))
and
(stuff(SQLString,1,charindex( ' and ',SQLString)+4, ' '))
group by
a.RowCode

/*
RowCode Amt
----------- -----------
1000 600