高手请进,复杂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
1100 400
1200 600
*/
[解决办法]
--也可以參考下面帖子的作法
http://community.csdn.net/Expert/topic/5338/5338927.xml?temp=.2254602
[解决办法]
declare @s varchar(8000)
set @s= ' '
select @s=@s+ 'select '+cast(rowcode as varchar)+ ' As rowcode,sum(amt) as Amt from B where '+SQLstring+ ' Union all '
from A
set @s= 'select * from ( '+substring(@s,1,len(@s)-10)+ ')T '
exec(@s)
原始数据：
RowCode SQLString
-------------------------------------------
1000acct Between 72110000 and 72112999
1100acct Between 72120000 and 72122999
1200acct Between 72130000 and 72132999
Acct Amt
--------------------------
72110000100
72111800200
72111900300
72120000400
72128000500
72130000600
--执行结果：
rowcode Amt
--------------
1000600
1100400
1200600
[解决办法]
CREATE TABLE TABLE_A([RowCode] INT, [SQLString] varchar(50))
INSERT INTO TABLE_A
SELECT 1000, 'acct Between 72110000 and 72112999 '
UNION ALL SELECT 1100, 'acct Between 72120000 and 72122999 '
UNION ALL SELECT 1200, 'acct Between 72130000 and 72132999 '
CREATE TABLE TABLE_B([Acct] INT, [Amt] INT)
INSERT INTO TABLE_B
SELECT 72110000, 100
UNION ALL SELECT 72111800, 200
UNION ALL SELECT 72111900, 300
UNION ALL SELECT 72120000, 400
UNION ALL SELECT 72128000, 500
UNION ALL SELECT 72130000, 600
GO
CREATE FUNCTION f_GetSum(@where VARCHAR(50))
RETURNS INT
AS
BEGIN
DECLARE @start INT
DECLARE @end INT
DECLARE @ret INT
SET @start = CAST(SUBSTRING(@where, CHARINDEX( 'Between ', @where) + 8, CHARINDEX( 'and ', @where) - CHARINDEX( 'Between ', @where) - 9) AS INT)
SET @end = CAST(SUBSTRING(@where, CHARINDEX( 'and ', @where) + 4, LEN(@where)) AS INT)
SET @ret = 0
SELECT @ret = SUM(Amt)
FROM TABLE_B
WHERE Acct BETWEEN @start AND @end
RETURN @ret
END
GO
SELECT RowCode, dbo.f_GetSum(SQLString)
FROM TABLE_A
DROP TABLE TABLE_A, TABLE_B
DROP FUNCTION f_GetSum
[解决办法]
create table A(RowCode int, SQLString varchar(50))
insert A select 1000, 'acct Between 72110000 and 72112999 '
union all select 1100, 'acct Between 72120000 and 72122999 '
union all select 1200, 'acct Between 72130000 and 72132999 '
go
create table B(Acct varchar(20), Amt int)
insert B select '72110000 ', 100
union all select '72111800 ', 200
union all select '72111900 ', 300
union all select '72120000 ', 400
union all select '72128000 ', 500
union all select '72130000 ', 600
go
select A.RowCode, Amt=sum(B.Amt) from A
inner join B on B.Acct between substring(SQLString, 14, 8) and substring(SQLString, 27, 8)
group by A.RowCode
--result
RowCode Amt
----------- -----------
1000 600
1100 400
1200 600
(3 row(s) affected)
