create table tableA(id varchar(20),dValue int,rtime datetime)
insert into tableA select '00001',100,'2005-12-10 15:00'
insert into tableA select '00002',200,'2005-12-10 15:00'
insert into tableA select '00003',150,'2005-12-10 15:00'
insert into tableA select '00001',120,'2005-12-10 15:50'
insert into tableA select '00002',230,'2005-12-10 15:50'
insert into tableA select '00003',190,'2005-12-10 15:50'
insert into tableA select '00001',180,'2005-12-10 17:50'
insert into tableA select '00002',290,'2005-12-10 17:50'
insert into tableA select '00003',390,'2005-12-10 17:50'
gocreate view v_A as
select
a.id,
a.dValue-(select top 1 dValue from tableA where id=a.id and rtime<a.rtime order by rtime desc) as dValue,
rtime
from
tableA a
where
exists(select 1 from tableA where id=a.id and rtime<a.rtime)
goselect * from v_A
/*
id dValue rtime
-------------------- ----------- ------------------------------------------------------
00001 20 2005-12-10 15:50:00.000
00002 30 2005-12-10 15:50:00.000
00003 40 2005-12-10 15:50:00.000
00001 60 2005-12-10 17:50:00.000
00002 60 2005-12-10 17:50:00.000
00003 200 2005-12-10 17:50:00.000
*/drop view v_A
drop table tableA
insert into tableA select '00001',100,'2005-12-10 15:00'
insert into tableA select '00002',200,'2005-12-10 15:00'
insert into tableA select '00003',150,'2005-12-10 15:00'
insert into tableA select '00001',120,'2005-12-10 15:50'
insert into tableA select '00002',230,'2005-12-10 15:50'
insert into tableA select '00003',190,'2005-12-10 15:50'
insert into tableA select '00001',180,'2005-12-10 17:50'
insert into tableA select '00002',290,'2005-12-10 17:50'
insert into tableA select '00003',390,'2005-12-10 17:50'
gocreate view v_A as
select
a.id,
a.dValue-(select top 1 dValue from tableA where id=a.id and rtime<a.rtime order by rtime desc) as dValue,
rtime
from
tableA a
where
exists(select 1 from tableA where id=a.id and rtime<a.rtime)
goselect * from v_A
/*
id dValue rtime
-------------------- ----------- ------------------------------------------------------
00001 20 2005-12-10 15:50:00.000
00002 30 2005-12-10 15:50:00.000
00003 40 2005-12-10 15:50:00.000
00001 60 2005-12-10 17:50:00.000
00002 60 2005-12-10 17:50:00.000
00003 200 2005-12-10 17:50:00.000
*/drop view v_A
drop table tableA
解决方案 »
- 救命啊: 怎样恢复误删的sql server数据表(用drop table 等命令删除的)?
- 作业创建的问题---建立作业的时候有没有语句删除同名作业
- 如何得到系统中的表的字段、字段类型、长度、是否为空、是否主键、缺省值等信息?
- 兄弟姐妹帮帮忙,一个很菜的问题
- 请帮我搞定这条头疼的SQL
- 如何防止与解决SQL发生死锁
- char和varchar的区别
- 请教:ODBC的问题?
- sql2000 在win2k下安装问题 救急
- SQL 7 复制问题。快照复制,为什么老是主键冲突?急!!!
- 如何使用LIKE方式查找带‘[’和‘]’的字符串?
- 一般的应用程序使用的sqlserver帐号我给他分配什么服务器角色呢?
create table A
(
id varchar(20),
dValue float,
rtime datetime
)insert A select '00001',100,'2005-12-10 15:00'
insert A select '00002',200,'2005-12-10 15:00'
insert A select '00003',150,'2005-12-10 15:00'
insert A select '00001',120,'2005-12-10 15:00'
insert A select '00002',230,'2005-12-10 15:00'
insert A select '00003',190,'2005-12-10 15:00'
insert A select '00001',180,'2005-12-10 15:00'
insert A select '00002',290,'2005-12-10 15:00'
insert A select '00003',390,'2005-12-10 15:00'create view V1
as
select (select count(*) from A where id=T.id and dValue<=T.dValue) as T_id,T.*
from
A Tcreate view v2
as
select T.id, T.dValue-(select top 1 dValue from V1 where id=T.id and T_id<T.T_id order by T_id DESC) as dValue,T.rtime
from
V1 T
where T.dValue-(select top 1 dValue from V1 where id=T.id and T_id<T.T_id order by T_id DESC) is not null
select * from V2
insert into tableA select '00001',100,'2005-12-10 15:00'
insert into tableA select '00002',200,'2005-12-10 15:00'
insert into tableA select '00003',150,'2005-12-10 15:00'
insert into tableA select '00001',120,'2005-12-10 15:50'
insert into tableA select '00002',230,'2005-12-10 15:50'
insert into tableA select '00003',190,'2005-12-10 15:50'
insert into tableA select '00001',180,'2005-12-10 17:50'
insert into tableA select '00002',290,'2005-12-10 17:50'
insert into tableA select '00003',390,'2005-12-10 17:50'
gocreate view v_A as
select
a.id,
a.dValue-b.dValue as dValue,
a.rtime
from
tableA a,tableA b
where
a.id=b.id
and
a.rtime>b.rtime
and
not exists(select 1 from tableA where id=a.id and rtime>b.rtime and rtime<a.rtime)
goselect * from v_A
/*
id dValue rtime
-------------------- ----------- ------------------------------------------------------
00001 20 2005-12-10 15:50:00.000
00002 30 2005-12-10 15:50:00.000
00003 40 2005-12-10 15:50:00.000
00001 60 2005-12-10 17:50:00.000
00002 60 2005-12-10 17:50:00.000
00003 200 2005-12-10 17:50:00.000
*/drop view v_A
drop table tableA
select
a.id,
a.dValue-b.dValue as dValue,
a.rtime
from
tableA a,tableA b
where
a.id=b.id
and
a.rtime>b.rtime
and
not exists(select 1 from tableA where id=a.id and rtime>b.rtime and rtime<a.rtime)
go
支持这个