acct_month charge 差额
200910 39.8 0
200909 64.4 -24.6
200908 70.75
200907 39.5
200906 34.5
200905 64.95
200904 52.65
200903 63.7
200902 51.75
200901 75
这是数据库中的2列字段 请问怎么用游标计算差额 就是差额那列 第一个为0 第二个为200910的费用减去200909的费用的值以此类推 我不会游标 希望能详细点 谢谢!!
200910 39.8 0
200909 64.4 -24.6
200908 70.75
200907 39.5
200906 34.5
200905 64.95
200904 52.65
200903 63.7
200902 51.75
200901 75
这是数据库中的2列字段 请问怎么用游标计算差额 就是差额那列 第一个为0 第二个为200910的费用减去200909的费用的值以此类推 我不会游标 希望能详细点 谢谢!!
--> 测试数据: [a]
if object_id('[a]') is not null drop table [a]
create table [a] (acct_month int,charge numeric(4,2),差额 numeric(4,2))
insert into [a]
select 200910,39.8,null union all
select 200909,64.4,null union all
select 200908,70.75,null union all
select 200907,39.5,null union all
select 200906,34.5,null union all
select 200905,64.95,null union all
select 200904,52.65,null union all
select 200903,63.7,null union all
select 200902,51.75,null union all
select 200901,75,nullupdate a set 差额=isnull(b.charge,a.charge)-isnull(a.charge,0) from a left join a b on a.acct_month=b.acct_month-1select * from [a]--结果:
acct_month charge 差额
----------- --------------------------------------- ---------------------------------------
200910 39.80 0.00
200909 64.40 -24.60
200908 70.75 -6.35
200907 39.50 31.25
200906 34.50 5.00
200905 64.95 -30.45
200904 52.65 12.30
200903 63.70 -11.05
200902 51.75 11.95
200901 75.00 -23.25
遊標不是好東西,,能不用就不用,,,
每行遊標都會獨立的發起一次select 很慢的說
if object_id('[tb]') is not null drop table [tb]
create table [tb]([acct_month] varchar(10),[charge] numeric(4,2),[差额] numeric(4,2))
insert [tb]
select '200910',39.8,null union all
select '200909',64.4,null union all
select '200908',70.75,null union all
select '200907',39.5,null union all
select '200906',34.5,null union all
select '200905',64.95,null union all
select '200904',52.65,null union all
select '200903',63.7,null union all
select '200902',51.75,null union all
select '200901',75,nullupdate t
set [差额]=isnull((select [charge]
from tb where [acct_month]=convert(varchar(6),dateadd(month,1,t.[acct_month]+'01'),112))-[charge],0)
from [tb] tselect * from [tb]-----------------------------
200910 39.80 .00
200909 64.40 -24.60
200908 70.75 -6.35
200907 39.50 31.25
200906 34.50 5.00
200905 64.95 -30.45
200904 52.65 12.30
200903 63.70 -11.05
200902 51.75 11.95
200901 75.00 -23.25
这个不是游标。就一普通的SQL语句。在存储过程中也可以使用。只需要这句就行了,更改表中差额字段:update 表名 set 差额=isnull(b.charge,a.charge)-isnull(a.charge,0) from 表名 left join 表名 b on a.acct_month=b.acct_month-1
--如果不是直接更改,而是得到差额这个字段。那就用:
select a.*,差额=isnull(b.charge,a.charge)-isnull(a.charge,0) from 表名 left join a 表名 on a.acct_month=b.acct_month-1
declare @tb table([acct_month] int,[charge] numeric(4,2))
insert @tb
select 200910,39.8 union all
select 200909,64.4 union all
select 200908,70.75 union all
select 200907,39.5 union all
select 200906,34.5 union all
select 200905,64.95 union all
select 200904,52.65 union all
select 200903,63.7 union all
select 200902,51.75 union all
select 200901,75select a.*,差额=isnull(b.[charge],a.[charge]) - isnull(a.[charge],0)
from @tb a left join @tb b
on a.[acct_month]= b.[acct_month]-1
/*
acct_month charge 差额
----------- --------------------------------------- ---------------------------------------
200910 39.80 0.00
200909 64.40 -24.60
200908 70.75 -6.35
200907 39.50 31.25
200906 34.50 5.00
200905 64.95 -30.45
200904 52.65 12.30
200903 63.70 -11.05
200902 51.75 11.95
200901 75.00 -23.25*/
acct_month charge 差额 比例20% 警告
----------- --------------------------------------- ---------------------------------
200910 39.80 0.00 7.96 NULL
200909 64.40 -24.60 12.88 降低20%
200908 70.75 -6.35 14.15
200907 39.50 31.25 7.9 提高20%
200906 34.50 5.00 6.9
200905 64.95 -30.45 12.99 降低20%
200904 52.65 12.30 10.53 提高20%
200903 63.70 -11.05 12.74
200902 51.75 11.95 10.35 提高20%
200901 75.00 -23.25 15 降低20%
比例20% 这个字段是 当前月费用的20% 然后拿差额和比例比较 负值的绝对值大于比例则在警告这个字段提示降低20% 正值的绝对值大于比例则在警告这个字段提示提高20% 问题是 怎么把警告这列 向上移动一行 而其他行不动 据说这个得游标做 我不知道行不
各位老大给解决下
select a.*,差额=isnull(b.charge,a.charge)-isnull(a.charge,0),警告=b.警告
from 表名 left join 表名 b on a.acct_month=b.acct_month-1
--好像反了,try
select a.*,差额=isnull(b.charge,a.charge)-isnull(a.charge,0),警告=a.警告
from 表名 left join 表名 b on a.acct_month=b.acct_month-1