列相减

[higoodstudydaydayup]

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
谢谢~分不够可以再加，解决马上给分

[青锋-SS]

ss

[青锋-SS]

偶来看看好人是谁.

[being21]

关注！！！

[kk19840210]

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 行受影响)

[kk19840210]

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 行受影响)

[flairsky]

[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]

这个不难吧，满足要求了吧

[higoodstudydaydayup]

谢谢~

[zccmy22]

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是表名，我也新数据库不久，试了通过，我也发上来看看吧。不知我的对不对。

[yyyyzzzz_2002]

/*  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

[yyyyzzzz_2002]

晕啊，第一行是最小的么