列相减

higoodstudydaydayup 2008-04-08 04:42:48
1. 列名 DoAt(datetime 型)
两数据 2008-03-09 08:51:07.000
2008-03-09 12:46:06.000

效果如 2008-03-09 08:51:07.000 ,2008-03-09 12:46:06.000,235

求怎么获取第二行减第一行的值(2008-03-09 12:46:06.000 - 2008-03-09 08:51:07.000)
行列转换?好像可以
2.第一部分是两列的,相对比较简单,第二问就是说如果行数不确定时,要求每行都减第一行的
三数据 2008-03-09 08:51:07.000
2008-03-09 12:46:06.000
2008-03-09 12:56:06.000
谢谢~分不够可以再加,解决马上给分
...全文
66 点赞 收藏 10
写回复
10 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
yyyyzzzz_2002 2008-04-08
晕啊,第一行是最小的么
回复
yyyyzzzz_2002 2008-04-08

/* 1. 列名 DoAt(datetime 型)
两数据 2008-03-09 08:51:07.000
2008-03-09 12:46:06.000

效果如 2008-03-09 08:51:07.000 ,2008-03-09 12:46:06.000,235

求怎么获取第二行减第一行的值(2008-03-09 12:46:06.000 - 2008-03-09 08:51:07.000)
行列转换?好像可以
2.第一部分是两列的,相对比较简单,第二问就是说如果行数不确定时,要求每行都减第一行的
三数据 2008-03-09 08:51:07.000
2008-03-09 12:46:06.000
2008-03-09 12:56:06.000
谢谢~分不够可以再加,解决马上给分
*/

use tempdb
go
if object_id('tempdb..#') is not null
drop table #

create table #(DoAt datetime)

insert into #
select dateadd(mi,1,getdate())
union all
select dateadd(mi,2,getdate())
union all
select dateadd(mi,3,getdate())
union all
select dateadd(mi,4,getdate())
union all
select dateadd(mi,5,getdate())
union all
select dateadd(mi,6,getdate())
union all
select dateadd(mi,7,getdate())
union all
select dateadd(mi,8,getdate())
union all
select dateadd(mi,9,getdate())



select b.DoAt as begin_date
, a.DoAt as end_date
,datediff(mi,a.DoAt,b.DoAt) as date_dif --根据你的需求确定需要什么单位
from # b
cross apply (select top 1 DoAt from #) a
where b.DoAt <>a.DoAt

回复
zccmy22 2008-04-08
select (select top 1 DoAt from trydata), Doat, DateDiff(mi, (select top 1 DoAt from TryData), DoAt) as NDoat from trydata where Doat != (select top 1 DoAt from trydata)

trydata是表名,我也新数据库不久,试了通过,我也发上来看看吧。不知我的对不对。
回复
谢谢~
回复
flairsky 2008-04-08
[Quote=引用 4 楼 kk19840210 的回复:]
SQL codecreatetable#t(DoAtdatetime)insertinto#tvalues('2008-03-09 08:51:07.000')insertinto#tvalues('2008-03-09 12:46:06.000')insertinto#tvalues('2008-03-09 12:56:06.000')selectdoat1=(selectmin(doat)from#t),doat,time=datediff(mi,(selectmin(doat)from#t),doat)from#twhere(selectmin(doat)from#t)<>doat


doat1 doat time----------------------- ----------------------…
[/Quote]

这个不难吧,满足要求了吧
回复
kk19840210 2008-04-08

create table #t(DoAt datetime)

insert into #t values('2008-03-09 08:51:07.000 ')
insert into #t values('2008-03-09 12:46:06.000 ')
insert into #t values('2008-03-09 12:56:06.000 ')


select doat1=(select min(doat) from #t),doat,time=datediff(mi,(select min(doat) from #t),doat) from #t

doat1 doat time
----------------------- ----------------------- -----------
2008-03-09 08:51:07.000 2008-03-09 08:51:07.000 0
2008-03-09 08:51:07.000 2008-03-09 12:46:06.000 235
2008-03-09 08:51:07.000 2008-03-09 12:56:06.000 245

(3 行受影响)
回复
kk19840210 2008-04-08

create table #t(DoAt datetime)

insert into #t values('2008-03-09 08:51:07.000 ')
insert into #t values('2008-03-09 12:46:06.000 ')
insert into #t values('2008-03-09 12:56:06.000 ')


select doat1=(select min(doat) from #t),doat,time=datediff(mi,(select min(doat) from #t),doat) from #t where (select min(doat) from #t)<>doat


doat1 doat time
----------------------- ----------------------- -----------
2008-03-09 08:51:07.000 2008-03-09 12:46:06.000 235
2008-03-09 08:51:07.000 2008-03-09 12:56:06.000 245

(2 行受影响)
回复
being21 2008-04-08
关注!!!
回复
青锋-SS 2008-04-08
偶来看看好人是谁.
回复
青锋-SS 2008-04-08
ss
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-04-08 04:42
社区公告
暂无公告