server sql 时间差分钟_高分！SQL语句，动态求时间差

SQL codeIF OBJECT_ID('tempdb..#FF') IS NOT NULL

DROP TABLE #FF

GO

CREATE TABLE #FF (Number INT,Date_Time DATETIME)

INSERT #FF

SELECT 1,'2012-1-7 02:05:57' UNION ALL

SELECT 2,'2012-1-7 03:38:52' UNION ALL

SELECT 4,'2012-1-7 04:17:07' UNION ALL

SELECT 3,'2012-1-7 04:48:32' UNION ALL

SELECT 2,'2012-1-7 09:18:34' UNION ALL

SELECT 1,'2012-1-7 11:55:38' UNION ALL

SELECT 1,'2012-1-7 16:47:56'

DECLARE @NUMBER INT,@QSSJ DATETIME,@JZSJ DATETIME

SELECT @NUMBER = 1,@QSSJ = '2012-1-7 9:00:00',@JZSJ = '2012-1-8 8:00:00'

SELECT

A.Number,

A.Date_Time,

B.Date_Time,

'分钟相差' = CAST(DATEDIFF(hh,B.Date_Time,A.Date_Time) AS VARCHAR(5))

+'小时'+CAST(DATEDIFF(mi,B.Date_Time,A.Date_Time)%60 AS VARCHAR(10))+'分'

+CAST(DATEDIFF(ss,B.Date_Time,A.Date_Time)%60 AS VARCHAR(10))+'秒'

FROM (

SELECT Number = MAX(Number),Date_Time = MAX(Date_Time),ROW = 0 FROM #FF WHERE Date_Time < @QSSJ AND Number = 1

UNION ALL

SELECT Number,Date_Time,ROW = ROW_NUMBER() OVER(ORDER BY(Date_Time ) ) FROM #FF WHERE Date_Time BETWEEN @QSSJ AND @JZSJ AND Number = 1

) A

JOIN

(

SELECT Number = MAX(Number),Date_Time = MAX(Date_Time),ROW = 0 FROM #FF WHERE Date_Time < @QSSJ AND Number = 1

UNION ALL

SELECT Number,Date_Time,ROW = ROW_NUMBER() OVER(ORDER BY(Date_Time ) ) FROM #FF WHERE Date_Time BETWEEN @QSSJ AND @JZSJ AND Number = 1

) B ON A.ROW = B.ROW+1

/*

Number Date_Time Date_Time 时间相差

----------- ----------------------- ----------------------- ---------------------------------

1 2012-01-07 11:55:38.000 2012-01-07 02:05:57.000 9小时50分41秒

1 2012-01-07 16:47:56.000 2012-01-07 11:55:38.000 5小时52分18秒

*/

------解决方案--------------------SQL codeDECLARE @NUMBER INT,@QSSJ DATETIME,@JZSJ DATETIME

SELECT @NUMBER = 1,@QSSJ = '2012-1-7 9:00:00',@JZSJ = '2012-1-8 8:00:00'

SELECT

A.Number,

A.Date_Time,

B.Date_Time,

'时间相差' = CAST(DATEDIFF(hh,B.Date_Time,A.Date_Time) AS VARCHAR(5))

+'小时'+CAST(DATEDIFF(mi,B.Date_Time,A.Date_Time)%60 AS VARCHAR(10))+'分'

+CAST(DATEDIFF(ss,B.Date_Time,A.Date_Time)%60 AS VARCHAR(10))+'秒'

FROM (

SELECT Number = MAX(Number),Date_Time = MAX(Date_Time),ROW = 0

FROM #FF WHERE Date_Time < @QSSJ AND Number = @NUMBER

UNION ALL

SELECT Number,Date_Time,ROW = ROW_NUMBER() OVER(ORDER BY(Date_Time ) )

FROM #FF WHERE Date_Time BETWEEN @QSSJ AND @JZSJ AND Number = @NUMBER

) A

JOIN

(

SELECT Number = MAX(Number),Date_Time = MAX(Date_Time),ROW = 0

FROM #FF

WHERE Date_Time < @QSSJ AND Number = @NUMBER

UNION ALL

SELECT Number,Date_Time,ROW = ROW_NUMBER() OVER(ORDER BY(Date_Time ) )

FROM #FF

WHERE Date_Time BETWEEN @QSSJ AND @JZSJ AND Number = @NUMBER

) B ON A.ROW = B.ROW+1

/*上面参数@NUMBER忘记放里面了*/