字段和数据类型大致如下,row字段不一定连续
d_rea,m_rea,lm_rea,y_rea,ly_rea,row,sign
22.00,123.00,55.00,100.00,101.00,1,+
-33.00,-123.01,-4.00,......, 1,-
14.00,22.00,.....................2,+.......要求: 取出相同行(row)的各项数据 进行符号(sign)运算,注意sign只能从第2行开始取,比如我取的是row=1的第1条数据,而我的符号row=1的第2条数据取,这里不知道如何更正,我的代码如下:
-----
declare cur_tmp cursor for
select distinct row from ht_tmp_no_h
open cur_tmp
fetch next from cur_tmp into @row
while @@fetch_status=0
begin
--赋初值
set @d_rea_sum=0.00
set @m_rea_sum=0.00
set @lm_rea_sum=0.00
set @y_rea_sum=0.00
set @ly_rea_sum=0.00
declare tmp cursor for
select isnull(d_rea,0.00),isnull(m_rea,0.00),isnull(lm_rea,0.00),isnull(y_rea,0.00),isnull(ly_rea,0.00),sign
from ht_tmp_no_h where row=@row
open tmp
fetch next from tmp into @d_rea,@m_rea,@lm_rea,@y_rea,@ly_rea,@sign
while @@fetch_status=0
begin
select @d_rea_sum =
(CASE
WHEN @sign='+' THEN @d_rea_sum +@d_rea
WHEN @sign='-' THEN @d_rea_sum-@d_rea
WHEN @sign='*' THEN @d_rea_sum*@d_rea
WHEN @sign='/' THEN @d_rea_sum/@d_rea
END)
if @ly_rea_sum>0
set @r_rea= @y_rea_sum/@ly_rea_sum -1 else
set @r_rea=0.00
fetch next from tmp into @d_rea,@m_rea,@lm_rea,@y_rea,@ly_rea,@sign
end
close tmp
deallocate tmp
d_rea,m_rea,lm_rea,y_rea,ly_rea,row,sign
22.00,123.00,55.00,100.00,101.00,1,+
-33.00,-123.01,-4.00,......, 1,-
14.00,22.00,.....................2,+.......要求: 取出相同行(row)的各项数据 进行符号(sign)运算,注意sign只能从第2行开始取,比如我取的是row=1的第1条数据,而我的符号row=1的第2条数据取,这里不知道如何更正,我的代码如下:
-----
declare cur_tmp cursor for
select distinct row from ht_tmp_no_h
open cur_tmp
fetch next from cur_tmp into @row
while @@fetch_status=0
begin
--赋初值
set @d_rea_sum=0.00
set @m_rea_sum=0.00
set @lm_rea_sum=0.00
set @y_rea_sum=0.00
set @ly_rea_sum=0.00
declare tmp cursor for
select isnull(d_rea,0.00),isnull(m_rea,0.00),isnull(lm_rea,0.00),isnull(y_rea,0.00),isnull(ly_rea,0.00),sign
from ht_tmp_no_h where row=@row
open tmp
fetch next from tmp into @d_rea,@m_rea,@lm_rea,@y_rea,@ly_rea,@sign
while @@fetch_status=0
begin
select @d_rea_sum =
(CASE
WHEN @sign='+' THEN @d_rea_sum +@d_rea
WHEN @sign='-' THEN @d_rea_sum-@d_rea
WHEN @sign='*' THEN @d_rea_sum*@d_rea
WHEN @sign='/' THEN @d_rea_sum/@d_rea
END)
if @ly_rea_sum>0
set @r_rea= @y_rea_sum/@ly_rea_sum -1 else
set @r_rea=0.00
fetch next from tmp into @d_rea,@m_rea,@lm_rea,@y_rea,@ly_rea,@sign
end
close tmp
deallocate tmp
解决方案 »
- 【求助】如何使用C#将多个DataTable中的数据信息读入到同一个Excel的不同sheet中,并重命名sheet表名?
- 请问以下几个锁的区别
- 关于连接远程数据库的问题
- 请问一下:复制中分发服务器导出的数据保存为XXX.BCP文件,请问一下这个文件如何打开.
- 求sql server 2000试题
- 请问用SQL语句怎样把数据库里面的数据按字符串长度的不同分别检索出来!
- 怎样定义这样的规则??
- 送100分给 warning(爱就爱了),快点进来。。
- 在sqlserver 7 中 为什么两数相除至返回整数部分? 如 select 1/3 返回0
- 请高手帮忙解决我的这个问题!
- 请教各位大哥,如何把2个sql 一次性执行阿。
- 没有为第 3 列(属于 '_x')指定列?关于union all
insert into @t select
22.00,123.00,55.00,100.00,101.00,1,'+' union all select
-33.00,-123.01,-4.00,null,null,1,'-' union all select
14.00,22.00,null,null,null,2,'+'
select
sum(case sign when '+' then isnull(d_rea,0) else -1*isnull(d_rea,0) end) d_rea,
sum(case sign when '+' then isnull(m_rea,0) else -1*isnull(m_rea,0) end) m_rea,
sum(case sign when '+' then isnull(lm_rea,0) else -1*isnull(lm_rea,0) end) lm_rea,
sum(case sign when '+' then isnull(y_rea,0) else -1*isnull(y_rea,0) end) y_rea,
sum(case sign when '+' then isnull(ly_rea,0) else -1*isnull(ly_rea,0) end) ly_rea,
row
from @t group by row
我的意思是:忽略每个row字段的第1条sign数据, 从第2条sign取符号(第1条sign符号全省略)