有一个科目余额表
大概用到的字段:
FACCOUNTI(科目内码),
FDEBIT(借方发生额),
FCREDIT(贷方发生额),
FYTDCREDIT(本年累计借方发生额),
FYTDCREDIT(本年贷方累计发生额),
FENDBALANCE(本期余额)
现在我想做个报表,报表有一个取数规则:例如1102 + 1103 (1102科目的FDEBIT加上1103的FCREDIT),但是这个取数规则用户是可以修改的,也可以是1102 + 1103 - 1110,
我该如何去设计数据表及怎样才能取出对应的数据?各位能否提供个思路?
大概用到的字段:
FACCOUNTI(科目内码),
FDEBIT(借方发生额),
FCREDIT(贷方发生额),
FYTDCREDIT(本年累计借方发生额),
FYTDCREDIT(本年贷方累计发生额),
FENDBALANCE(本期余额)
现在我想做个报表,报表有一个取数规则:例如1102 + 1103 (1102科目的FDEBIT加上1103的FCREDIT),但是这个取数规则用户是可以修改的,也可以是1102 + 1103 - 1110,
我该如何去设计数据表及怎样才能取出对应的数据?各位能否提供个思路?
FACCOUNTI,字段,运算符(也可以是正负号,或者系数)
'1102','FDEBIT','1'
'1103','FCREDIT','1'
'1110','FCREDIT','-1'变量这个表,拼出类似的串。
(select (1)*FDEBIT from 表 where FACCOUNTI='1102')
+(select (1)*FCREDIT from 表 where FACCOUNTI='1103')
+(select (-1)*FCREDIT from 表 where FACCOUNTI='1110')
红色都是读规则表拼的。
执行动态sql。
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)
)
GOINSERT 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.94SELECT * FROM #TEMP_BALANCE客户需求报表:1结果:1000的FDEBIT + 1001的FCREDIT (正确结果:6900203.45)
2结果:1004的FDEBIT + 1001的FCREDIT - 1005的FENDBALANCE (正确结果:5712844.45)
……
……
像结果后面的取数规则是由客户自己定义的,我们只负责按照他的规则取出数据,我的问题是该如何存储用户的取数规则以及正确得到相应的结果。
2结果:1004的FDEBIT + 1001的FCREDIT - 1005的FENDBALANCE (正确结果:5712844.45)
……楼主是不是结果给错了,怎么一个是按fcredit算,一个是fdebit啊!
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)
)
goinsert 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
godeclare @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
2结果:1004的FDEBIT + 1001的FCREDIT - 1005的FENDBALANCE (正确结果:5712844.45)纠结中如果是 1001+1000+1005-1006呢?
设置某一个字段col存贮下面的字符串值'(select FDEBIT where FACCOUNTID=1000)+(select FCREDIT where FACCOUNTID=1001')
exec('select '+col)