取出表中每个月最后数值与上个月最后数字之差
表如下:
id value date
-------------------
1 2.3 2008-3-3
2 4.2 2008-3-18
3 5.3 2008-3-30
4 1.1 2008-4-1
5 3.3 2008-4-12
6 5.6 2008-4-28
7 3.2 2008-5-3
8 2.3 2008-5-20结果:
value
0.3 算法:(5.6-5.3)
1.1 算法:(6.7-5.6)
表如下:
id value date
-------------------
1 2.3 2008-3-3
2 4.2 2008-3-18
3 5.3 2008-3-30
4 1.1 2008-4-1
5 3.3 2008-4-12
6 5.6 2008-4-28
7 3.2 2008-5-3
8 2.3 2008-5-20结果:
value
0.3 算法:(5.6-5.3)
1.1 算法:(6.7-5.6)
declare @t table(id int, value float, date datetime)
-------------------
insert @t select 1 , 2.3, '2008-3-3'
insert @t select 2 , 4.2, '2008-3-18'
insert @t select 3 , 5.3, '2008-3-30'
insert @t select 4 , 1.1, '2008-4-1'
insert @t select 5, 3.3, '2008-4-12'
insert @t select 6, 5.6, '2008-4-28'
insert @t select 7, 3.2, '2008-5-3'
insert @t select 8, 2.3, '2008-5-20'
select max(value)value,convert(varchar(7),date,120) date,id=identity(int,1,1) into # from @t group by convert(varchar(7),date,120)
select value=b.value-a.value from # a inner join # b on a.id=b.id-1
drop table #
/*value
-----------------------------------------------------
0.29999999999999982
-2.3999999999999995*/
INSERT INTO #A SELECT 1,2.3,'2008-03-03'
INSERT INTO #A SELECT 2,4.2,'2008-03-18'
INSERT INTO #A SELECT 3,5.3,'2008-03-30'
INSERT INTO #A SELECT 4,1.1,'2008-04-01'
INSERT INTO #A SELECT 5,3.3,'2008-04-12'
INSERT INTO #A SELECT 6,5.6,'2008-04-28'
INSERT INTO #A SELECT 7,3.2,'2008-05-03'
INSERT INTO #A SELECT 8,6.7,'2008-05-20'SELECT IDENTITY(INT,1,1)SORT,* INTO #B FROM #A A
WHERE (SELECT COUNT(*) FROM #A WHERE YEAR(S_DATE)=YEAR(A.S_DATE) AND MONTH(S_DATE)=MONTH(A.S_DATE) AND S_DATE>A.S_DATE)<1
SELECT QTY-(SELECT QTY FROM #B WHERE SORT=B.SORT-1) FROM #B B
WHERE SORT<>1DROP TABLE #A,#B
value
0.3 算法:(5.6-5.3)
1.1 算法:(6.7-5.6)你上面写的是6.7-5.6,数据中没有6.7阿,我把测试数据中最后一条修改了。2.3-〉6.7了
create MyTable(Three_monthValue int ,Three_Four int,Four_monthValue int,Four_Five int,Five_monthValue int)
---追加数据insert into MyTable()
select Three_monthValue = case when Tab.MM = '3' then value else 0 end,
0,
Four_monthValue = case when Tab.MM = '4' then value else 0 end
0,
Five_monthValue = case when Tab.MM = '5' then value else 0 end
(
---取到最大的日期和对应数据
select value,Tdate,MONTH(Tdate) as MM
from table,
(select MAX(Tdate) As MaxTime,MONTH(Tdate) As V_Month
from table
group by MONTH(Tdate)
) As BB
where table.Tdate = MM.MaxTime
and table.MM=BB.V_Month
) AS Tab ---更新数据差
update MyTable
set Three_Four = Four_monthValue-Three_monthValue,
Four_Five = Five_monthValue-Four_monthValue
from MyTable