比较SQL Server中的第一行和第n行
问题描述：
我只需要按ID在一个分区中的前100个密钥和即将到来的101个密钥之间的日期时间差异，即如果下一个密钥值也为101，则采用该关键日期时间值，即id = 1。 101键即将到来的100键也一样。我尝试使用窗口函数，但没有得到确切的答案。比较SQL Server中的第一行和第n行
ID Name Key datetime
1 AAA 100 2016-07-01 09:32:48.000
1 AAA 100 2016-07-01 13:31:02.000
1 AAA 100 2016-07-01 14:10:57.000
1 AAA 101 2016-07-01 15:12:09.000
1 AAA 100 2016-07-01 15:12:17.000
1 AAA 100 2016-07-01 15:18:36.000
1 AAA 101 2016-07-01 15:34:16.000
2 BBB 100 2016-07-04 09:26:15.000
2 BBB 100 2016-07-04 13:40:52.000
2 BBB 101 2016-07-04 14:18:26.000
2 BBB 101 2016-07-04 18:34:29.000
2 BBB 100 2016-07-04 18:34:32.000
2 BBB 101 2016-07-04 23:04:32.000
进出料应该像
ID Name Key datetime In Out
1 AAA 100 2016-07-01 09:32:48.000 05:39:21
1 AAA 100 2016-07-01 13:31:02.000
1 AAA 100 2016-07-01 14:10:57.000
1 AAA 101 2016-07-01 15:12:09.000 00:06:27
1 AAA 100 2016-07-01 15:12:17.000 00:21:59
1 AAA 100 2016-07-01 15:18:36.000
1 AAA 101 2016-07-01 15:34:16.000
2 BBB 100 2016-07-04 09:26:15.000 09:08:14
2 BBB 100 2016-07-04 13:40:52.000
2 BBB 101 2016-07-04 14:18:26.000
2 BBB 101 2016-07-04 18:34:29.000 00:00:03
2 BBB 100 2016-07-04 18:34:32.000 04:30:00
2 BBB 101 2016-07-04 23:04:32.000
答
下面是做这件事（假设SQL Server 2012中或更高版本）
首先，创建和填充示例表（请，请将您的未来问题省略我们这一步）
DECLARE @T AS TABLE
(
Id int,
Name char(3),
[Key] int,
[datetime] DateTime
)
INSERT INTO @T VALUES
(1, 'AAA', 100, '2016-07-01 09:32:48.000'),
(1, 'AAA', 100, '2016-07-01 13:31:02.000'),
(1, 'AAA', 100, '2016-07-01 14:10:57.000'),
(1, 'AAA', 101, '2016-07-01 15:12:09.000'),
(1, 'AAA', 100, '2016-07-01 15:12:17.000'),
(1, 'AAA', 100, '2016-07-01 15:18:36.000'),
(1, 'AAA', 101, '2016-07-01 15:34:16.000'),
(2, 'BBB', 100, '2016-07-04 09:26:15.000'),
(2, 'BBB', 100, '2016-07-04 13:40:52.000'),
(2, 'BBB', 101, '2016-07-04 14:18:26.000'),
(2, 'BBB', 101, '2016-07-04 18:34:29.000'),
(2, 'BBB', 100, '2016-07-04 18:34:32.000'),
(2, 'BBB', 101, '2016-07-04 23:04:32.000')
查询：
SELECT Id,
Name,
[Key],
[DateTime],
CASE WHEN [Key] = 100 AND ISNULL(LAG([Key]) OVER(PARTITION BY Id ORDER BY [DateTime]), 101) <> 100 THEN
CAST(
(SELECT TOP 1 [DateTime]
FROM @T subQuery
WHERE subQuery.[Key] = 101
AND subQuery.Id = outerQuery.Id
AND subQuery.[DateTime] > outerQuery.[DateTime]
ORDER BY [DateTime]
) - [DateTime]
As Time)
ELSE
NULL
END As [In],
CASE WHEN [Key] = 101 AND ISNULL(LAG([Key]) OVER(PARTITION BY Id ORDER BY [DateTime]), 100) <> 101 THEN
CAST(
(SELECT TOP 1 [DateTime]
FROM @T subQuery
WHERE subQuery.[Key] = 100
AND subQuery.Id = outerQuery.Id
AND subQuery.[DateTime] > outerQuery.[DateTime]
ORDER BY [DateTime]
) - [DateTime]
As Time)
ELSE
NULL
END As [Out]
FROM @T outerQuery
结果：
Id Name Key DateTime In Out
1 AAA 100 01.07.2016 09:32:48 05:39:21
1 AAA 100 01.07.2016 13:31:02
1 AAA 100 01.07.2016 14:10:57
1 AAA 101 01.07.2016 15:12:09 00:00:08
1 AAA 100 01.07.2016 15:12:17 00:21:59
1 AAA 100 01.07.2016 15:18:36
1 AAA 101 01.07.2016 15:34:16
2 BBB 100 04.07.2016 09:26:15 04:52:11
2 BBB 100 04.07.2016 13:40:52
2 BBB 101 04.07.2016 14:18:26 04:16:06
2 BBB 101 04.07.2016 18:34:29
2 BBB 100 04.07.2016 18:34:32 04:30:00
2 BBB 101 04.07.2016 23:04:32
答
万一没人走来帮忙，你可以试试这个。这是一团糟，但与测试数据一起工作。
rextester：http://rextester.com/MPD86507
create table KeySwipeLog (Id int, Name char(3),KeyCard int, dt datetime);
insert into KeySwipeLog values
(1,'aaa',100,'2016-07-01 09:32:48.000')
,(1,'aaa',100,'2016-07-01 13:31:02.000')
,(1,'aaa',100,'2016-07-01 14:10:57.000')
,(1,'aaa',101,'2016-07-01 15:12:09.000')
,(1,'aaa',100,'2016-07-01 15:12:17.000')
,(1,'aaa',100,'2016-07-01 15:18:36.000')
,(1,'aaa',101,'2016-07-01 15:34:16.000')
,(2,'bbb',100,'2016-07-04 09:26:15.000')
,(2,'bbb',100,'2016-07-04 13:40:52.000')
,(2,'bbb',101,'2016-07-04 14:18:26.000')
,(2,'bbb',101,'2016-07-04 18:34:29.000')
,(2,'bbb',100,'2016-07-04 18:34:32.000')
,(2,'bbb',101,'2016-07-04 23:04:32.000');
with cte as (
select
Id
, Name
, Keycard
, Dt
, ignore = case
when KeyCard = 100
and isnull(lag(k.KeyCard) over (partition by k.Id order by k.dt),0)!=100
then 0
when KeyCard = 101 and isnull(lead(k.KeyCard) over (partition by k.Id order by k.dt),0)!=101
then 0
else 1
end
/* When the next Keycard is also 101, skip the current record for calculating in times*/
, in_ignore = case
when KeyCard = 101
and isnull(lead(k.KeyCard) over (partition by k.Id order by k.dt),0)!=101
then 0
--when KeyCard = 100 then null
else 1
end
/* When the next Keycard is also 100, skip the current record for calculating out times*/
, out_ignore = case
when KeyCard = 100
and isnull(lead(k.KeyCard) over (partition by k.Id order by k.dt),0) = 100
then 1
--when KeyCard = 101 then 1
else 0
end
from KeySwipeLog as k
)
select
k.Id
, k.Name
, k.KeyCard
, k.dt
--, k.ignore
--, k.in_ignore
--, k.out_ignore
, [in] =case
when k.KeyCard=100
and i.dt is not null
and k.ignore = 0
then convert(varchar(8),convert(time(0),i.dt-k.dt))
else ''
end
, out=case
when k.KeyCard=101
and o.dt is not null
and k.ignore = 0
then convert(varchar(8),convert(time(0),o.dt-k.dt))
else ''
end
--, i.*
--, o.*
from cte as k
outer apply (
select top 1 *
from cte as x
where x.Id=k.Id
and x.dt>k.dt
and x.keycard!=k.keycard
and x.in_ignore=0
order by dt asc
) as i
outer apply (
select top 1 *
from cte as x
where x.Id=k.Id
and x.dt>k.dt
and x.keycard!=k.keycard
and x.out_ignore=0
order by dt asc
) as o
order by k.Id, k.dt
答
DECLARE @T AS TABLE
(
Id int,
Name char(3),
[Key] int,
[datetime] DateTime
)
INSERT INTO @T VALUES
(1, 'AAA', 100, '2016-07-01 09:32:48.000'),
(1, 'AAA', 100, '2016-07-01 13:31:02.000'),
(1, 'AAA', 100, '2016-07-01 14:10:57.000'),
(1, 'AAA', 101, '2016-07-01 15:12:09.000'),
(1, 'AAA', 100, '2016-07-01 15:12:17.000'),
(1, 'AAA', 100, '2016-07-01 15:18:36.000'),
(1, 'AAA', 101, '2016-07-01 15:34:16.000'),
(2, 'BBB', 100, '2016-07-04 09:26:15.000'),
(2, 'BBB', 100, '2016-07-04 13:40:52.000'),
(2, 'BBB', 101, '2016-07-04 14:18:26.000'),
(2, 'BBB', 101, '2016-07-04 18:34:29.000'),
(2, 'BBB', 100, '2016-07-04 18:34:32.000'),
(2, 'BBB', 101, '2016-07-04 23:04:32.000')
;with cte as
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY ID,NAMe ORDER BY [datetime])RN,
CASE WHEN LAG([Key],1) OVER(PARTITION BY ID,NAMe ORDER BY [datetime]) = [KEY] THEN 0 ELSE 1 END IsDiffer
FROM @t
)
SELECT c1.*,
CASE WHEN c1.[Key] = 100 THEN
CAST(DATEDIFF(ss,c1.[datetime],E.[datetime]) /3600 AS VARCHAR) +':'+
CAST((DATEDIFF(ss,c1.[datetime],E.[datetime]) %3600)/60 AS VARCHAR) +':'+
CAST((DATEDIFF(ss,c1.[datetime],E.[datetime]) %60) AS VARCHAR) END [IN],
CASE WHEN c1.[Key] = 101 THEN
CAST(DATEDIFF(ss,c1.[datetime],E.[datetime]) /3600 AS VARCHAR) +':'+
CAST((DATEDIFF(ss,c1.[datetime],E.[datetime]) %3600)/60 AS VARCHAR) +':'+
CAST((DATEDIFF(ss,c1.[datetime],E.[datetime]) %60) AS VARCHAR) END [OUT]
FROM CTE c1
OUTER APPLY
(
SELECT TOP 1 * FROM cte c2
where c1.id = c2.id and c1.name = c2.name
and c2.IsDiffer = 1 and c1.IsDiffer = 1
and c1.rn <c2.rn
ORDER BY RN
)E
添加预期的输出 –
古温德·辛格datetime列值之差即差 '2016年7月1日09：32：48.000' 和“2016年7月1日15 ：12：09.000' – Jalandhar
请编辑您的问题并解释输出 – TheGameiswar