我有一个使用'withCTE'函数的现有查询。该查询返回我想要的结果,但是,该查询也会在任何给定日期的特定时间段内返回重复的值。举个例子,查询返回如下结果:
rn | NAME MACHINE SUPERVISOR LAST_UPDATE
1 | NAME_1 T_001 SUP_1 2015-10-14 09:55:26.637
2 | NAME_2 T_002 SUP_2 2015-10-14 11:54:07.087
3 | NAME_2 T_002 SUP_3 2015-10-14 12:02:31.663
4 | NAME_3 T_003 SUP_4 2015-10-14 22:53:28.253
5 | NAME_4 T_005 SUP_4 2015-10-14 09:55:47.293
6 | NAME_3 T_003 SUP_3 2015-10-15 01:59:00.000
7 | NAME_3 T_003 SUP_3 2015-10-15 10:19:00.000
现在,这是我需要实现的规则:我需要在9AM-9PM和9PM-9AM的时间跨度上获得具有相同名称和机器的最新行。这是我需要达到的结果:
rn | NAME MACHINE SUPERVISOR LAST_UPDATE
1 | NAME_1 T_001 SUP_1 2015-10-14 09:55:26.637
3 | NAME_2 T_002 SUP_3 2015-10-14 12:02:31.663
5 | NAME_4 T_005 SUP_4 2015-10-14 09:55:47.293
6 | NAME_3 T_003 SUP_3 2015-10-15 01:59:00.000
7 | NAME_3 T_003 SUP_3 2015-10-15 10:19:00.000
请注意,第2行已被删除,因为第2行和第3行具有相同的名称和机器,并且它们在上午9点至晚上9点的时间范围内,所以我只需要第3行的最新版本。
看第4、6和7行。这些行具有相同的名称和机器,但只有第4行和第6行是9PM-9AM时间跨度内的行。因此,只保留了第6行。第7行没有受到影响,因为它在自己的时间跨度上是唯一的(名称和机器)。
如果你能在这件事上帮我,我将不胜感激。提前谢谢。
试试这个:
declare @tbl table(id int, NAME varchar(100), MACHINE varchar(100), SUPERVISOR varchar(100), LAST_UPDATE datetime)
insert @tbl (id, NAME, MACHINE, SUPERVISOR, LAST_UPDATE)
values(1, 'NAME_1', 'T_001','SUP_1','20151014 09:55:26.637'),
(2,'NAME_2','T_002','SUP_2','20151014 11:54:07.087'),
(3,'NAME_2','T_002','SUP_3','20151014 12:02:31.663'),
(4,'NAME_3','T_003','SUP_4','20151014 22:53:28.253'),
(5,'NAME_4','T_005','SUP_4','20151014 09:55:47.293'),
(6,'NAME_3','T_003','SUP_3','20151015 01:59:00.000'),
(7,'NAME_3','T_003','SUP_3','20151015 10:19:00.000')
select *,DATEADD(HOUR,9,CAST(CAST(t1.LAST_UPDATE as date) as datetime)), DATEADD(HOUR,33,CAST(CAST(t1.LAST_UPDATE as date) as datetime))
from @tbl t1
where not exists(select *
from @tbl t2
where t2.id<>t1.id
and t2.NAME=t1.NAME
and t2.MACHINE=t1.MACHINE
and t2.LAST_UPDATE>=t1.LAST_UPDATE
and t2.LAST_UPDATE>=DATEADD(HOUR,9,CAST(CAST(CASE WHEN DATEPART(HOUR,t1.LAST_UPDATE)>9 THEN t1.LAST_UPDATE ELSE DATEADD(DAY,-1,t1.LAST_UPDATE) END as date) as datetime))
and t2.LAST_UPDATE<DATEADD(HOUR,33,CAST(CAST(CASE WHEN DATEPART(HOUR,t1.LAST_UPDATE)>9 THEN t1.LAST_UPDATE ELSE DATEADD(DAY,-1,t1.LAST_UPDATE) END as date) as datetime))
)