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 = 0

select @result = @result + DATEDIFF(SECOND, Time1, time2)
from TEST

select @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 ALL
SELECT '2011/10/18 15:10:00','2011/10/18 15:28:00' UNION ALL
SELECT '2011/10/18 15:38:00','2011/10/18 15:48:00' UNION ALL
SELECT '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          30
3           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) t1
left join (select ROW_NUMBER() OVER (ORDER BY id) AS ROWNUMBER, Time1, Time2 from TestTime) t2 on t2.ROWNUMBER = t1.ROWNUMBER + 1) as t