SQL 小弟我想获得列之间时间差的平均值

select avg(datediff(yy,'2010-06-01','2012-05-01'))
------解决方案--------------------------------------------------------
探讨

SQL用：datediff()方法


SQL code


select datediff(yy,'2010-06-01','2012-05-01')
select datediff(mm,'2010-06-01','2012-05-01')
select datediff(dd,'2010-06-01','2012-05-01')

------解决方案--------------------------------------------------------
SQL code
CREATE TABLE TEST (ID int,Time1 datetime,Time2 datetime);  INSERT INTO TEST VALUES(1, '18.10.2011 14:57:00', '18.10.2011 14:58:00');INSERT INTO TEST VALUES(2, '18.10.2011 15:10:00', '18.10.2011 15:28:00');INSERT INTO TEST VALUES(3, '18.10.2011 15:38:00', '18.10.2011 15:48:00');INSERT INTO TEST VALUES(4, '18.10.2011 15:58:00', '18.10.2011 16:08:00');declare @result int = 0select @result = @result + DATEDIFF(SECOND, Time1, time2)from TESTselect @result / (select COUNT(ID) from Test)
------解决方案--------------------------------------------------------
SQL code
DECLARE @table table(id int identity(1,1),time1 datetime,time2 datetime)INSERT INTO @table(time1,time2)SELECT '2011/10/18 14:57:00','2011/10/18 14:58:00' UNION ALLSELECT '2011/10/18 15:10:00','2011/10/18 15:28:00' UNION ALLSELECT '2011/10/18 15:38:00','2011/10/18 15:48:00' UNION ALLSELECT '2011/10/18 15:58:00','2011/10/18 16:08:00';WITH CTE AS(    SELECT *,ROW_NUMBER() OVER(ORDER BY id) AS Row FROM @table),RS AS(    SELECT id,DATEDIFF(n,time1,time11) timespan1,DATEDIFF(n,time2,time22) timespan2    FROM(SELECT *,(SELECT time1 FROM CTE WHERE Row=T.Row+1) time11,(SELECT time2 FROM CTE WHERE Row=T.Row+1) time22 FROM CTE T WHERE Row%2=1) TT)SELECT * FROM rs/*(4 行受影响)id          timespan1   timespan2----------- ----------- -----------1           13          303           20          20(2 行受影响)*/---------------;WITH CTE AS(    SELECT *,ROW_NUMBER() OVER(ORDER BY id) AS Row FROM @table),RS AS(    SELECT id,DATEDIFF(n,time1,time11) timespan1,DATEDIFF(n,time2,time22) timespan2    FROM(SELECT *,(SELECT time1 FROM CTE WHERE Row=T.Row+1) time11,(SELECT time2 FROM CTE WHERE Row=T.Row+1) time22 FROM CTE T WHERE Row%2=1) TT)SELECT AVG(timespan1) timeavg1,AVG(timespan2) timeavg1 FROM rs/*timeavg1    timeavg1----------- -----------16          25(1 行受影响)*/
------解决方案--------------------------------------------------------
探讨

引用:

你求日期的平均值是以年、月还是天为最小单位，可以不写存储过程也一样的方便。问题是你需要什么

按照我的需求话，最小单位是秒。
我的需求是第二行的time 1与上一行的time2 的差值,然后将所有差值求平均值

------解决方案--------------------------------------------------------
SQL code
select AVG(diff) from (select DATEDIFF(SECOND, t1.Time2, t2.Time1) as diff from (select ROW_NUMBER() OVER (ORDER BY id) AS ROWNUMBER, Time1, Time2 from TestTime) t1left join (select ROW_NUMBER() OVER (ORDER BY id) AS ROWNUMBER, Time1, Time2 from TestTime) t2 on t2.ROWNUMBER = t1.ROWNUMBER + 1) as t