数据表如下字段:
id,code,dao;
我想把id+1的code-id的code 结果放入id.dao里面
也就是说把 ID排序后,后一条记录的code减前一条记录的code,获得的值放入前一条记录的dao中
不知哪位高手有解决的SQL语句
给你逐行相减的例例表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
.
.这两个查询语句应该怎么写呢?
if object_id('pubs..tb') is not null
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
P14=(SELECT TOP 1 p14 FROM TB WHERE nyr>A.nyr order by nyr )-p14
FROM TB Adrop table tbP14
-----------
-11
-9
19
21
-22
12
-26
29
NULL(所影响的行数为 9 行)if object_id('pubs..tb') is not null
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 行)
if object_id('pubs..tb') is not null
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 行)
id,code,dao;
我想把id+1的code-id的code 结果放入id.dao里面
也就是说把 ID排序后,后一条记录的code减前一条记录的code,获得的值放入前一条记录的dao中
不知哪位高手有解决的SQL语句
给你逐行相减的例例表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
.
.这两个查询语句应该怎么写呢?
if object_id('pubs..tb') is not null
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
P14=(SELECT TOP 1 p14 FROM TB WHERE nyr>A.nyr order by nyr )-p14
FROM TB Adrop table tbP14
-----------
-11
-9
19
21
-22
12
-26
29
NULL(所影响的行数为 9 行)if object_id('pubs..tb') is not null
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 行)
if object_id('pubs..tb') is not null
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 行)
dao=(SELECT TOP 1 code FROM TB WHERE id>A.id order by id )-code
FROM TB A
dao=(SELECT TOP 1 code FROM TB WHERE id>A.id order by id )-code
FROM TB A
declare @code int,@dao intupdate #tmp
set @dao = @code-code,@code = code ,dao = @daoupdte t
set t.dao = x.dao
from tab t
join #tmp x on x.id = t.iddrop table #tmp
insert into @table
select 1,30,0
union all select 3,40,0
union all select 2,20,0
union all select 6,18,0
union all select 5,12,0
union all select 4,44,0
union all select 8,33,0
union all select 7,65,0select id,code,(select top 1 code from @table where id>a.id order by id)-code as dao
from @table a
order by id/*结果
id code dao
----------- ----------- -----------
1 30 -10
2 20 20
3 40 4
4 44 -32
5 12 6
6 18 47
7 65 -32
8 33 NULL
*/