高分!SQL语句,动态求时间差
高分求助!SQL语句,动态求时间差
在SQL数据表中有数据:
Date_Time Number
2012-1-7 02:05:57 3
2012-1-7 03:38:52 13
2012-1-7 04:17:07 7
2012-1-7 04:48:32 5
2012-1-7 09:18:34 9
2012-1-7 11:27:55 10
2012-1-7 11:55:38 1
2012-1-7 16:47:56 1
2012-1-7 17:14:37 11
2012-1-7 18:38:02 6
2012-1-7 19:14:04 4
2012-1-7 23:24:13 12
2012-1-7 23:53:45 16
2012-1-8 02:31:22 3
2012-1-8 04:04:34 13
2012-1-8 04:40:32 7
2012-1-8 05:13:03 5
2012-1-8 09:54:32 15
2012-1-8 10:15:32 9
2012-1-8 11:45:17 8
2012-1-8 12:17:26 10
2012-1-8 14:08:11 2
2012-1-8 14:12:39 7
2012-1-8 14:40:15 3
2012-1-8 17:11:52 1
2012-1-8 17:39:49 11
2012-1-8 19:05:55 6
2012-1-8 19:42:22 4
2012-1-8 23:48:32 12
sql查询特定的数据如查询 2012-1-7 9:00:00到2012-1-8 8:00:00
Number值,与上一个时间的时间差,一般周期大概是25h,但是还是有特殊情况
想得出结果= Number值,时间差1,时间差2
sql语句如何处理呢?请教高手!!!!
------解决方案--------------------
------解决方案--------------------
在SQL数据表中有数据:
Date_Time Number
2012-1-7 02:05:57 3
2012-1-7 03:38:52 13
2012-1-7 04:17:07 7
2012-1-7 04:48:32 5
2012-1-7 09:18:34 9
2012-1-7 11:27:55 10
2012-1-7 11:55:38 1
2012-1-7 16:47:56 1
2012-1-7 17:14:37 11
2012-1-7 18:38:02 6
2012-1-7 19:14:04 4
2012-1-7 23:24:13 12
2012-1-7 23:53:45 16
2012-1-8 02:31:22 3
2012-1-8 04:04:34 13
2012-1-8 04:40:32 7
2012-1-8 05:13:03 5
2012-1-8 09:54:32 15
2012-1-8 10:15:32 9
2012-1-8 11:45:17 8
2012-1-8 12:17:26 10
2012-1-8 14:08:11 2
2012-1-8 14:12:39 7
2012-1-8 14:40:15 3
2012-1-8 17:11:52 1
2012-1-8 17:39:49 11
2012-1-8 19:05:55 6
2012-1-8 19:42:22 4
2012-1-8 23:48:32 12
sql查询特定的数据如查询 2012-1-7 9:00:00到2012-1-8 8:00:00
Number值,与上一个时间的时间差,一般周期大概是25h,但是还是有特殊情况
想得出结果= Number值,时间差1,时间差2
sql语句如何处理呢?请教高手!!!!
------解决方案--------------------
------解决方案--------------------
- SQL code
--没有看明白...是不是这样一个思路 ;with cte as ( select rownumber() over(order by date_time) as sn,date_time,number from tb ) select a.date_time,a.number,datediff(hh,a.date_time,b.date_time) as difftime,(b.number-a.number) as number_diff from cte as a join cte as b on a.sn=b.sn+1 --没有在分析器里写..因为我不懂你的思路.不知道写的对不对..大致就这个思路
------解决方案--------------------
- SQL code
IF 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 code
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 = @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忘记放里面了*/