例表D1
nyr P8 P14 P201/1/05 35 32 40
1/2/05 53 21 18
1/3/05 64 12 60
1/4/05 37 31 13
1/5/05 25 52 25
1/6/05 11 30 59
1/7/05 56 42 37
1/8/05 28 16 69
1/9/05 38 45 15
.
.
.我想查询后得到表中前后相邻P14字段的差值cp14(要后一天减去前一天的)表,即为如下结果:
cP14 -11
-9
19
21
-22
12
-26
29
.
.
.
还要在D1表中实现查询刚才求得的表cp14两相邻间的差值(也是后一条减去前一条的)。(拿D1表中1,2,3,4,5号的P14记录来说:就是要实现(3号-2号)-(2号-1号),(4号-3号)-(3号-2号),(5号-4号)-(4号-3号)..依此类推最终得到如下结果: ccp14 2
28
2
-43
34
-38
55
.
.这两个查询语句应该怎么写呢?
drop table tb
gocreate table tb
(
nyr datetime,
P8 int,
P14 int,
P20 int
)insert into tb(nyr,P8,P14,P20) values('1/1/05', 35, 32, 40)
insert into tb(nyr,P8,P14,P20) values('1/2/05', 53, 21, 18)
insert into tb(nyr,P8,P14,P20) values('1/3/05', 64, 12, 60)
insert into tb(nyr,P8,P14,P20) values('1/4/05', 37, 31, 13)
insert into tb(nyr,P8,P14,P20) values('1/5/05', 25, 52, 25)
insert into tb(nyr,P8,P14,P20) values('1/6/05', 11, 30, 59)
insert into tb(nyr,P8,P14,P20) values('1/7/05', 56, 42, 37)
insert into tb(nyr,P8,P14,P20) values('1/8/05', 28, 16, 69)
insert into tb(nyr,P8,P14,P20) values('1/9/05', 38, 45, 15)select id = identity(int,1,1),* into tb1 from tb
select id = identity(int,1,1),* into tb2 from tbselect tb2.p14-tb1.p14 as p14
from tb1,tb2
where tb1.id = tb2.id - 1drop table tb,tb1,tb2p14
-----------
-11
-9
19
21
-22
12
-26
29(所影响的行数为 8 行)
drop table tb
gocreate table tb
(
nyr datetime,
P8 int,
P14 int,
P20 int
)insert into tb(nyr,P8,P14,P20) values('1/1/05', 35, 32, 40)
insert into tb(nyr,P8,P14,P20) values('1/2/05', 53, 21, 18)
insert into tb(nyr,P8,P14,P20) values('1/3/05', 64, 12, 60)
insert into tb(nyr,P8,P14,P20) values('1/4/05', 37, 31, 13)
insert into tb(nyr,P8,P14,P20) values('1/5/05', 25, 52, 25)
insert into tb(nyr,P8,P14,P20) values('1/6/05', 11, 30, 59)
insert into tb(nyr,P8,P14,P20) values('1/7/05', 56, 42, 37)
insert into tb(nyr,P8,P14,P20) values('1/8/05', 28, 16, 69)
insert into tb(nyr,P8,P14,P20) values('1/9/05', 38, 45, 15)select id = identity(int,1,1),* into tb1 from tb
select id = identity(int,1,1),* into tb2 from tbselect id = identity(int,1,1) , tb2.p14-tb1.p14 as p14
into tb3
from tb1,tb2
where tb1.id = tb2.id - 1select id = identity(int,1,1) , tb2.p14-tb1.p14 as p14
into tb4
from tb1,tb2
where tb1.id = tb2.id - 1select tb4.p14-tb3.p14 as p14
from tb3,tb4
where tb3.id = tb4.id - 1drop table tb,tb1,tb2,tb3,tb4p14
-----------
2
28
2
-43
34
-38
55(所影响的行数为 7 行)
create table tb
(
nyr datetime,
P8 int,
P14 int,
P20 int
)insert into tb(nyr,P8,P14,P20) values('1/1/05', 35, 32, 40)
insert into tb(nyr,P8,P14,P20) values('1/2/05', 53, 21, 18)
insert into tb(nyr,P8,P14,P20) values('1/3/05', 64, 12, 60)
insert into tb(nyr,P8,P14,P20) values('1/4/05', 37, 31, 13)
insert into tb(nyr,P8,P14,P20) values('1/5/05', 25, 52, 25)
insert into tb(nyr,P8,P14,P20) values('1/6/05', 11, 30, 59)
insert into tb(nyr,P8,P14,P20) values('1/7/05', 56, 42, 37)
insert into tb(nyr,P8,P14,P20) values('1/8/05', 28, 16, 69)
insert into tb(nyr,P8,P14,P20) values('1/9/05', 38, 45, 15)SELECT
P14=(SELECT TOP 1 p14 FROM TB WHERE nyr>A.nyr order by nyr )-p14
FROM TB A
declare @d1 table( nyr datetime,p8 int,p14 int,p20 int)
insert into @d1 select convert(varchar,'2005-01-01',23),35,32,40
insert into @d1 select convert(varchar,'2005-01-02',23), 53, 21, 18
insert into @d1 select convert(varchar,'2005-01-03',23), 64, 12, 60
insert into @d1 select convert(varchar,'2005-01-04',23), 37, 31, 13
insert into @d1 select convert(varchar,'2005-01-05',23), 25, 52, 25
insert into @d1 select convert(varchar,'2005-01-06',23), 11, 30, 59
insert into @d1 select convert(varchar,'2005-01-07',23), 56, 42, 37
insert into @d1 select convert(varchar,'2005-01-08',23), 28, 16, 69
insert into @d1 select convert(varchar,'2005-01-09',23), 38, 45, 15select * from @d1--select *
--from @d1 a where not exists(select 1 from @d1 b where a.nyr>b.nyr)
--union
select a.nyr,
sum(b.p14)-sum(a.p14) cP14
from @d1 a,@d1 b
where a.nyr=b.nyr-1
group by a.nyr
select a.nyr,
sum(b.p14)-sum(a.p14) cP14
from @d1 a,@d1 b
where a.nyr=b.nyr-2
group by a.nyr
union all select '1/2/05', 53, 21, 18
union all select '1/3/05', 64, 12, 60
union all select '1/4/05', 37, 31, 13
union all select '1/5/05', 25, 52, 25
union all select '1/6/05', 11, 30, 59
union all select '1/7/05', 56, 42, 37
union all select '1/8/05', 28, 16, 69
union all select '1/9/05', 38, 45, 15--表中前后相邻P14字段的差值cp14(要后一天减去前一天的)select (select P14 from D1 where nyr=A.nyr+1)-P14 as P14 from D1 as A
where nyr<>(select max(nyr) from D1)
select ID, (select P14 from #T where ID=A.ID+2)-2*(select P14 from #T where ID=A.ID+1)+P14 from #T as A
select ID=identity(int, 1, 1), (select P14 from D1 where nyr=A.nyr+1)-P14 as P14 into #T
from D1 as A
where nyr<>(select max(nyr) from D1)--表cp14两相邻间的差值(也是后一条减去前一条的)
select ID, (select P14 from #T where ID=A.ID+2)-2*(select P14 from #T where ID=A.ID+1)+P14 from #T as Adrop table #T