表的结构(海量的数据库,目前有3000万条记录)
ID CarKey Date
1 15022104271 2007-12-11 14:59:00
2 15022104271 2007-12-11 15:00:00
3 15022104271 2007-12-11 15:03:00
23 15022104275 2007-12-11 15:53:00
25 15022104275 2007-12-11 16:05:00
29 15022104275 2007-12-11 16:11:00
通过sql查询得到的结果是1 15022104271 2007-12-11 14:59:00
2 15022104271 00:01:00
3 15022104271 00:03:00
23 15022104275 2007-12-11 15:53:00
25 15022104275 00:12:00
29 15022104275 00:06:00有没有比较好的思路,方法,有实现方法最好
上次发了,本以为不错,还是不行的。
参考http://topic.csdn.net/u/20081105/14/d2b17b5f-0171-4cdd-b0b1-5c0886e996b7.html
ID CarKey Date
1 15022104271 2007-12-11 14:59:00
2 15022104271 2007-12-11 15:00:00
3 15022104271 2007-12-11 15:03:00
23 15022104275 2007-12-11 15:53:00
25 15022104275 2007-12-11 16:05:00
29 15022104275 2007-12-11 16:11:00
通过sql查询得到的结果是1 15022104271 2007-12-11 14:59:00
2 15022104271 00:01:00
3 15022104271 00:03:00
23 15022104275 2007-12-11 15:53:00
25 15022104275 00:12:00
29 15022104275 00:06:00有没有比较好的思路,方法,有实现方法最好
上次发了,本以为不错,还是不行的。
参考http://topic.csdn.net/u/20081105/14/d2b17b5f-0171-4cdd-b0b1-5c0886e996b7.html
-- Author: liangCK 小梁
-- Date : 2008-11-19 21:44:04
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (ID INT,CarKey BIGINT,Date DATETIME)
INSERT INTO @T
SELECT 1,15022104271,'2007-12-11 14:59:00' UNION ALL
SELECT 2,15022104271,'2007-12-11 15:00:00' UNION ALL
SELECT 3,15022104271,'2007-12-11 15:03:00' UNION ALL
SELECT 23,15022104275,'2007-12-11 15:53:00' UNION ALL
SELECT 25,15022104275,'2007-12-11 16:05:00' UNION ALL
SELECT 29,15022104275,'2007-12-11 16:11:00'--SQL查询如下:SELECT
t.ID,
t.CarKey,
t.Date,
ISNULL(b.tt,CONVERT(VARCHAR(20),t.Date,120)) AS preDate
FROM @T AS t
OUTER APPLY
(
SELECT TOP(1) CONVERT(VARCHAR(20),t.Date-Date,108) AS tt
FROM @T
WHERE CarKey=t.CarKey
AND ID<t.ID
ORDER BY Date DESC
) AS b/*
ID CarKey Date preDate
----------- -------------------- ----------------------- --------------------
1 15022104271 2007-12-11 14:59:00.000 2007-12-11 14:59:00
2 15022104271 2007-12-11 15:00:00.000 00:01:00
3 15022104271 2007-12-11 15:03:00.000 00:03:00
23 15022104275 2007-12-11 15:53:00.000 2007-12-11 15:53:00
25 15022104275 2007-12-11 16:05:00.000 00:12:00
29 15022104275 2007-12-11 16:11:00.000 00:06:00(6 行受影响)
*/
declare @t table(ID int,CarKey bigint,Date datetime)
insert into @t values(1 ,15022104271,'2007-12-11 14:59:00')
insert into @t values(2 ,15022104271,'2007-12-11 15:00:00')
insert into @t values(3 ,15022104271,'2007-12-11 15:03:00')
insert into @t values(23,15022104275,'2007-12-11 15:53:00')
insert into @t values(25,15022104275,'2007-12-11 16:05:00')
insert into @t values(29,15022104275,'2007-12-11 16:11:00') select t.ID,t.CarKey,isnull((select top 1 convert(char(19),t.Date-Date,108) from @t where CarKey=t.CarKey and ID<t.ID order by ID DESC),convert(char(19),t.Date,120)) as Date
from @t t
/*ID CarKey Date
----------- -------------------- -------------------
1 15022104271 2007-12-11 14:59:00
2 15022104271 00:01:00
3 15022104271 00:03:00
23 15022104275 2007-12-11 15:53:00
25 15022104275 00:12:00
29 15022104275 00:06:00 (所影响的行数为 6 行)*/
--> 测试数据: @s
declare @s table (ID int,CarKey bigint,Date datetime)
insert into @s
select 1,15022104271,'2007-12-11 14:59:00' union all
select 2,15022104271,'2007-12-11 15:00:00' union all
select 3,15022104271,'2007-12-11 15:03:00' union all
select 23,15022104275,'2007-12-11 15:53:00' union all
select 25,15022104275,'2007-12-11 16:05:00' union all
select 29,15022104275,'2007-12-11 16:11:00'select id,carkey,date=isnull(cast(datediff(mi,(select top 1 date from @s where carkey=a.carkey and date<a.date order by date desc),a.date) as varchar),Convert(varchar(20),a.date,120))
from @s a
CarKey Date
15022104271 2007-12-11 14:59:00
15022104271 2007-12-11 15:00:00
15022104271 2007-12-11 15:03:00
15022104275 2007-12-11 15:53:00
15022104275 2007-12-11 16:05:00
15022104275 2007-12-11 16:11:00 通过sql查询得到的结果是 15022104271 00:59:00
15022104271 00:01:00
15022104271 00:03:00
15022104271 1:27:00
15022104275 01:53:00
15022104275 00:12:00
15022104275 00:06:00
对的,是是最近的数据进行相减,这个是我的错! 15022104275 00:17:00
CarKey Date
15022104271 2007-12-11 14:59:00
15022104271 2007-12-11 15:00:00
15022104271 2007-12-11 15:03:00
15022104275 2007-12-11 15:53:00
15022104275 2007-12-11 16:05:00
15022104275 2007-12-11 16:11:00 通过sql查询得到的结果是 15022104271 00:59:00
15022104271 00:01:00
15022104271 00:03:00
15022104271 1:27:00
15022104275 01:53:00
15022104275 00:12:00
15022104275 00:06:00
15022104275 00:17:00
对的,是最近的数据进行相减,这个是我的错!
笔误,真的不好意思.
--比较笨的方法:::
DECLARE @T TABLE (ID INT,CarKey BIGINT,Date DATETIME)
INSERT INTO @T
SELECT 1,15022104271,'2007-12-11 14:59:00' UNION ALL
SELECT 2,15022104271,'2007-12-11 15:00:00' UNION ALL
SELECT 3,15022104271,'2007-12-11 15:03:00' UNION ALL
SELECT 23,15022104275,'2007-12-11 15:53:00' UNION ALL
SELECT 25,15022104275,'2007-12-11 16:05:00' UNION ALL
SELECT 29,15022104275,'2007-12-11 16:11:00'select * from
(select c.id,c.carkey,c.date, convert(char(19),c.Date-d.Date,108) as 时间值 from (
select (select count(*)-1 from @t where carkey=a.carkey and id <=a.id) as [count],* from @t a) c,
(select (select count(*) from @t where carkey=a.carkey and id <=a.id) as [count],* from @t a) d
where c.[count]=d.[count] and c.carkey=d.carkey
union all
select c.id,c.carkey,c.date, convert(char(19),c.Date,120) 时间值 from (
select (select count(*)-1 from @t where carkey=a.carkey and id <=a.id) as [count],* from @t a) c
where c.[count]=0) e
order by id/*id carkey date 时间值
----------- -------------------- ----------------------- -------------------
1 15022104271 2007-12-11 14:59:00.000 2007-12-11 14:59:00
2 15022104271 2007-12-11 15:00:00.000 00:01:00
3 15022104271 2007-12-11 15:03:00.000 00:03:00
23 15022104275 2007-12-11 15:53:00.000 2007-12-11 15:53:00
25 15022104275 2007-12-11 16:05:00.000 00:12:00
29 15022104275 2007-12-11 16:11:00.000 00:06:00 (6 行受影响)
*/