22,210
社区成员
发帖
与我相关
我的任务
分享
'(select FDEBIT where FACCOUNTID=1000)+(select FCREDIT where FACCOUNTID=1001')
exec('select '+col)
create table #temp_balance
(
faccountid int,
fdebit decimal(13,2),
fcredit decimal(13,2),
fytddebit decimal(13,2),
fytdcredit decimal(13,2),
fendbalance decimal(13,2)
)
go
insert into #temp_balance
select 1000,327359.00,328989.82,327359.00,328989.82,40250.63
union all
select 1001,7509115.10,6572844.45,7509115.10,6572844.45,9366373.50
union all
select 1004,0.00,0.00,0.00,0.00,1000000.00
union all
select 1005,6919858.13,7508525.00,6919858.13,7508525.00,43790107.43
union all
select 1006,0.00,0.00,0.00,0.00,-860000.00
union all
select 1009,201861.64,285499.59,201861.64,285499.59,1289985.48
union all
select 1011,5659809.04,4207507.22,5659809.04,4207507.22,9010930.77
union all
select 1014,4629674.33,4182286.37,4629674.33,4182286.37,836511.24
union all
select 1015,312136.43,85802.36,312136.43,85802.36,336651.40
union all
select 1017,3999585.68,4175146.27,3999585.68,4175146.27,480897.94
go
declare @sql varchar(100)
set @sql = '1000+1001'
set @sql = (case left(@sql,1) when '-' then '-' else '+' end)+replace(replace(@sql,'+',',+'),'-',',-')
select isnull((select sum(fcredit) from #temp_balance where charindex(',+'+ltrim(faccountid)+',',','+@sql+',') > 0),0)
- isnull((select sum(fcredit) from #temp_balance where charindex(',-'+ltrim(faccountid)+',',','+@sql+',') > 0),0)
drop table #temp_balance
/**********
---------------------------------------
6901834.27
IF OBJECT_ID(N'TEMPDB..#TEMP_BALANCE') IS NOT NULL
DROP TABLE #TEMP_BALANCE
GO
CREATE TABLE #TEMP_BALANCE
(
FACCOUNTID INT,
FDEBIT DECIMAL(13,2),
FCREDIT DECIMAL(13,2),
FYTDDEBIT DECIMAL(13,2),
FYTDCREDIT DECIMAL(13,2),
FENDBALANCE DECIMAL(13,2)
)
GO
INSERT INTO #TEMP_BALANCE
SELECT 1000,327359.00,328989.82,327359.00,328989.82,40250.63
UNION ALL
SELECT 1001,7509115.10,6572844.45,7509115.10,6572844.45,9366373.50
UNION ALL
SELECT 1004,0.00,0.00,0.00,0.00,1000000.00
UNION ALL
SELECT 1005,6919858.13,7508525.00,6919858.13,7508525.00,43790107.43
UNION ALL
SELECT 1006,0.00,0.00,0.00,0.00,-860000.00
UNION ALL
SELECT 1009,201861.64,285499.59,201861.64,285499.59,1289985.48
UNION ALL
SELECT 1011,5659809.04,4207507.22,5659809.04,4207507.22,9010930.77
UNION ALL
SELECT 1014,4629674.33,4182286.37,4629674.33,4182286.37,836511.24
UNION ALL
SELECT 1015,312136.43,85802.36,312136.43,85802.36,336651.40
UNION ALL
SELECT 1017,3999585.68,4175146.27,3999585.68,4175146.27,480897.94
SELECT * FROM #TEMP_BALANCE
客户需求报表:
1结果:1000的FDEBIT + 1001的FCREDIT (正确结果:6900203.45)
2结果:1004的FDEBIT + 1001的FCREDIT - 1005的FENDBALANCE (正确结果:5712844.45)
……
……
像结果后面的取数规则是由客户自己定义的,我们只负责按照他的规则取出数据,我的问题是该如何存储用户的取数规则以及正确得到相应的结果。