T/SQL -时间相关计算
T/SQL -时间相关计算
提问于 2015-01-20 16:22:02
IF OBJECT_ID('Tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable
(
[ID] INT NOT NULL ,
[Value] VARCHAR(50) NULL ,
[Date] DATE NULL ,
[Time] TIME(7) NULL ,
[Duration] INT NULL ,
[srcFile] VARCHAR(50) NULL,
)
INSERT #TempTable
( [ID], [Value], [Date], [Time], [Duration], [srcFile] )
VALUES ( 1, N'One', CAST(N'2014-07-29' AS DATE), CAST(N'23:34:00' AS TIME), 1710, N'sF1' ),
( 2, N'One', CAST(N'2014-07-30' AS DATE), CAST(N'00:00:10' AS TIME), 1710, N'sF1' ),
( 3, N'One', CAST(N'2014-07-30' AS DATE), CAST(N'01:30:00' AS TIME), 1710, N'sF1' ),
( 4, N'One', CAST(N'2014-07-30' AS DATE), CAST(N'01:54:00' AS TIME), 1710, N'sF1' ),
( 5, N'One', CAST(N'2014-07-30' AS DATE), CAST(N'13:30:00' AS TIME), 1710, N'sF1' ),
( 6, N'One', CAST(N'2014-07-30' AS DATE), CAST(N'13:57:00' AS TIME), 1710, N'sF2' ),
( 7, N'One', CAST(N'2014-07-30' AS DATE), CAST(N'23:34:00' AS TIME), 1710, N'sF1' ),
( 8, N'One', CAST(N'2014-07-31' AS DATE), CAST(N'00:00:10' AS TIME), 1710, N'sF2' ),
( 9, N'One', CAST(N'2014-07-31' AS DATE), CAST(N'00:10:10' AS TIME), 1710, N'sF3' ),
( 10, N'One', CAST(N'2014-08-01' AS DATE), CAST(N'00:00:00' AS TIME), 1710, N'sF2' ),
( 11, N'One', CAST(N'2014-08-01' AS DATE), CAST(N'00:00:00' AS TIME), 1710, N'sF1' ),
( 12, N'One', CAST(N'2014-08-01' AS DATE), CAST(N'01:00:00' AS TIME), 1710, N'sF3' ),
( 13, N'One', CAST(N'2014-08-01' AS DATE), CAST(N'01:00:00' AS TIME), 1710, N'sF4' ),
( 14, N'Two', CAST(N'2014-08-01' AS DATE), CAST(N'00:01:00' AS TIME), 1710, N'sF2' )
SELECT *
FROM #TempTable基表
ID Value Date Time Duration srcFile
1 One 7/29/2014 23:34:00 1710 sF1
2 One 7/30/2014 0:00:10 1710 sF1
3 One 7/30/2014 1:30:00 1710 sF1
4 One 7/30/2014 1:54:00 1710 sF1
5 One 7/30/2014 13:30:00 1710 sF1
6 One 7/30/2014 13:57:00 1710 sF2
7 One 7/30/2014 23:34:00 1710 sF1
8 One 7/31/2014 0:00:10 1710 sF2
9 One 8/1/2014 0:00:00 1710 sF2
10 Two 8/1/2014 0:01:00 1710 sF2
11 One 8/1/2014 0:00:00 1710 sF1要求：
当值+日期+时间匹配时，Dup输出:用1标记isDup标志，为两个或多个dupFIle条件匹配的记录标记srcFile。
当任何两个或两个以上记录的值匹配和日期+时间都在日期+时间加(+)持续时间内时，则重叠(注意:当所有匹配的记录都不能是DUP...they时，overlap..but重叠至少可以有一个唯一的记录和多个在持续时间范围内的欺骗)。输出:使用1标记isOverlap标志，对重叠条件匹配的两个或多个记录使用overlapFile标记和srcFile标记。
这就是我试过的
;WITH dupCTE AS (
SELECT ID, Value, [Date], [Time], Duration, srcFile
,CASE
WHEN COUNT(*) OVER (PARTITION BY Value, [Date], [Time]) > 1 THEN 1
ELSE 0
END AS isDup
,CASE WHEN COUNT(*) OVER (PARTITION BY Value, [Date], [Time]) > 1 THEN STUFF((SELECT ' - ' + srcFile
FROM #TempTable T
WHERE T.Value = TT.Value
AND T.[Date] = TT.[Date]
AND T.[Time] = TT.[Time]
FOR XML PATH('')), 1, 3, '')
ELSE NULL
END AS dupFIle
FROM #TempTable TT
)
, overlapCTE AS (
SELECT A. ID, A.Value, A.[Date], A.[Time], A.Duration, A.srcFile, A.isDup, A.dupFIle
,CASE WHEN B.ID IS NOT NULL THEN 1
ELSE 0
END AS 'isOverlap'
,CASE WHEN b.ID IS NOT NULL THEN STUFF((SELECT ' - ' + srcFile
FROM #TempTable T
WHERE T.Value = A.Value
AND ((CAST(CAST(B.[Date] AS VARCHAR(10)) + ' ' + CAST(B.[Time] AS VARCHAR(16)) AS DateTime2) > CAST(CAST(A.[Date] AS VARCHAR(10)) + ' ' + CAST(A.[Time] AS VARCHAR(16)) AS DateTime2) AND CAST(CAST(B.[Date] AS VARCHAR(10)) + ' ' + CAST(B.[Time] AS VARCHAR(16)) AS DateTime2) < DATEADD(SECOND, A.Duration, CAST(CAST(A.[Date] AS VARCHAR(10)) + ' ' + CAST(A.[Time] AS VARCHAR(16)) AS DateTime2)))
OR (CAST(CAST(A.[Date] AS VARCHAR(10)) + ' ' + CAST(A.[Time] AS VARCHAR(16)) AS DateTime2) > CAST(CAST(B.[Date] AS VARCHAR(10)) + ' ' + CAST(B.[Time] AS VARCHAR(16)) AS DateTime2) AND CAST(CAST(A.[Date] AS VARCHAR(10)) + ' ' + CAST(A.[Time] AS VARCHAR(16)) AS DateTime2) < DATEADD(SECOND, B.Duration, CAST(CAST(B.[Date] AS VARCHAR(10)) + ' ' + CAST(B.[Time] AS VARCHAR(16)) AS DateTime2))))
FOR XML PATH('')), 1, 3, '')
ELSE NULL
END AS 'overlapFiles'
FROM dupCTE A LEFT JOIN dupCTE B
ON A.Value = B.Value
AND ((CAST(CAST(B.[Date] AS VARCHAR(10)) + ' ' + CAST(B.[Time] AS VARCHAR(16)) AS DateTime2) > CAST(CAST(A.[Date] AS VARCHAR(10)) + ' ' + CAST(A.[Time] AS VARCHAR(16)) AS DateTime2) AND CAST(CAST(B.[Date] AS VARCHAR(10)) + ' ' + CAST(B.[Time] AS VARCHAR(16)) AS DateTime2) < DATEADD(SECOND, A.Duration, CAST(CAST(A.[Date] AS VARCHAR(10)) + ' ' + CAST(A.[Time] AS VARCHAR(16)) AS DateTime2)))
OR (CAST(CAST(A.[Date] AS VARCHAR(10)) + ' ' + CAST(A.[Time] AS VARCHAR(16)) AS DateTime2) > CAST(CAST(B.[Date] AS VARCHAR(10)) + ' ' + CAST(B.[Time] AS VARCHAR(16)) AS DateTime2) AND CAST(CAST(A.[Date] AS VARCHAR(10)) + ' ' + CAST(A.[Time] AS VARCHAR(16)) AS DateTime2) < DATEADD(SECOND, B.Duration, CAST(CAST(B.[Date] AS VARCHAR(10)) + ' ' + CAST(B.[Time] AS VARCHAR(16)) AS DateTime2))))
WHERE A.isDup = 1 OR
B.ID IS NOT NULL
)
SELECT * FROM overlapCTE
DROP TABLE #TempTable电流输出
ID Value Date Time Duration srcFile isDup dupFIle isOverlap overlapFiles
1 One 2014-07-29 23:34:00 1710 sF1 0 NULL 1 sF1 - sF1 - sF1 - sF1 - sF1 - sF2 - sF1 - sF2 - sF2 - sF1
2 One 2014-07-30 00:00:10 1710 sF1 0 NULL 1 sF1 - sF1 - sF1 - sF1 - sF1 - sF2 - sF1 - sF2 - sF2 - sF1
3 One 2014-07-30 01:30:00 1710 sF1 0 NULL 1 sF1 - sF1 - sF1 - sF1 - sF1 - sF2 - sF1 - sF2 - sF2 - sF1
4 One 2014-07-30 01:54:00 1710 sF1 0 NULL 1 sF1 - sF1 - sF1 - sF1 - sF1 - sF2 - sF1 - sF2 - sF2 - sF1
5 One 2014-07-30 13:30:00 1710 sF1 0 NULL 1 sF1 - sF1 - sF1 - sF1 - sF1 - sF2 - sF1 - sF2 - sF2 - sF1
6 One 2014-07-30 13:57:00 1710 sF2 0 NULL 1 sF1 - sF1 - sF1 - sF1 - sF1 - sF2 - sF1 - sF2 - sF2 - sF1
7 One 2014-07-30 23:34:00 1710 sF1 0 NULL 1 sF1 - sF1 - sF1 - sF1 - sF1 - sF2 - sF1 - sF2 - sF2 - sF1
8 One 2014-07-31 00:00:10 1710 sF2 0 NULL 1 sF1 - sF1 - sF1 - sF1 - sF1 - sF2 - sF1 - sF2 - sF2 - sF1
9 One 2014-08-01 00:00:00 1710 sF2 1 sF2 - sF1 0 NULL
11 One 2014-08-01 00:00:00 1710 sF1 1 sF2 - sF1 0 NULL期望输出
ID Value Date Time Duration srcFile isDup dupFIle isOverLap overlapFile
1 One 2014-07-29 24:34:00 1710 sF1 0 NULL 1 sF1 - sF1
2 One 2014-07-30 00:00:10 1710 sF1 0 NULL 1 sF1 - sF1
3 One 2014-07-30 01:30:00 1710 sF1 0 NULL 1 sF1 - sF1
4 One 2014-07-30 01:54:00 1710 sF1 0 NULL 1 sF1 - sF1
5 One 2014-07-30 13:30:00 1710 sF1 0 NULL 1 sF1 - sF2
6 One 2014-07-30 13:57:00 1710 sF2 0 NULL 1 sF2 - sF1
7 One 2014-07-30 24:34:00 1710 sF1 0 NULL 1 sF1 - sF2
8 One 2014-07-31 00:00:10 1710 sF2 0 NULL 1 sF2 - sF1
9 One 2014-08-01 00:00:00 1710 sF2 1 sF2 - sF1 0 NULL
10 Two 2014-08-01 00:01:00 1710 sF2 0 NULL 0 NULL
11 One 2014-08-01 00:00:00 1710 sF1 1 sF1 - sF2 0 NULL我不符合要求。任何帮助都将不胜感激。
谢谢
更新:新增当前输出
Update2:在Dup中发现了一个错误(使用ID而不是值)。所需的输出仍然不理想。
Update3:进步的人，我们非常接近。现在，重叠逻辑正在“工作”。一个主要的问题是overlapFIles。它应该只列出那些相互重叠的记录的文件(现在，它列出了来自overlapCTE输出的所有文件，而不是专门列出那些在内容查询中满足WHERE的文件)。还有，有什么方法可以让这个独特的记录被列出吗？
Update4:添加了更多记录，以查看重复查询和重叠查询是否可以容纳两个以上的记录。
回答 4
Stack Overflow用户
回答已采纳
发布于 2015-01-28 12:10:58
这应该会给你你想要的东西：
With CTE as (Select T.ID ID1, T.srcFile + ' - ' + c.srcFile over1, '1' as isDup from
#TempTable T
INNER JOIN #TempTable c on T.Value = c.Value and c.ID <> T.ID and (Cast(C.Date as datetime) + Cast(C.Time as datetime)) = (Cast(T.Date as datetime) + Cast(T.Time as datetime))),
CTE2 as
(Select T.ID ID1, c.ID ID2, T.srcFile + ' - ' + c.srcFile over1, c.srcFile + ' - ' + T.srcFile over2, '1' as isOverLap from
#TempTable T
INNER JOIN #TempTable c on T.Value = c.Value and c.ID <> T.ID
Where DateAdd(second, c.Duration, Cast(C.Date as datetime) + Cast(C.Time as datetime)) > (Cast(T.Date as datetime) + Cast(T.Time as datetime)) and (Cast(C.Date as datetime) + Cast(C.Time as datetime)) < (Cast (T.Date as datetime) + Cast(T.Time as datetime)))
Select T.*, ISNULL((Select top 1 c.isDup from CTE c where c.ID1 = T.ID) ,0) isDup
,(Select substring((select ',' + c1.over1 as [text()] from CTE c1 where c1.ID1 = T.ID for xml path ('')),2,1000)) dupFile
,ISNULL((select Top 1 case isOverLap when 1 then 1 else 0 end from CTE2 c where c.ID1 = T.ID or C.ID2 = T.ID),0) isOverLap
,(Select substring((select case when T.ID = C.ID1 then ',' + c.over1 else ',' + c.over2 end as [text()] from CTE2 c where c.ID1 = T.ID or C.ID2 = T.ID for xml path('')),2,1000)) OverlapFile
from #TempTable TStack Overflow用户
发布于 2015-01-29 18:58:34
代码遵循您的要求(希望如此)。我通过添加更多的重叠和重复来测试它，它不仅适用于两个重复的、重叠的文件(例如srcFile='sF3')，而且使用这些观察：
- DupFile -列表总是按文件名排序的
- 重叠文件--如果只有一个文件，就没有对"sF1 - sF1"，而只有"sF1“--我不确定这对于生产嘌呤是否必要，但可能会被调整(目前还没有)。
with rows
(
select [ID],[Value], [Date], [Time], [Duration], [srcFile],
cast(cast([date] as varchar(10))+' ' +cast(time as varchar(8)) as datetime) as datetime,
dateadd(ss,-duration,cast(cast([date] as varchar(10))+' ' +cast(time as varchar(8)) as datetime)) as date_from,
dateadd(ss,duration,cast(cast([date] as varchar(10))+' ' +cast(time as varchar(8)) as datetime)) as date_to
from #TempTable
)
, dups
as
(
SELECT [value], [Date], [Time]
FROM rows
group by [value], [Date], [Time]
having count([ID])>1
)
, dups_files
as
(
select r.*
,
STUFF((select ' - '+d.srcFile
FROM rows as d
WHERE (r.[value]=d.value and r.time=d.time
and r.date=d.date)
order by d.srcFile
FOR xml path('')),1,3,'') as dupFile
FROM dups r
)
, duplicities
as
(
select a.id, d.dupFile
from rows a join dups_files d
on ( a.value=d.value and a.date=d.date
and a.time=d.time)
)
, overlaps_pairs
as
(
select f.id as id_a, d.id as id_b, f.srcfile as srcfile_a, d.srcfile as srcfile_b
from rows f JOIN rows d
ON (f.id<>d.id
AND f.value=d.value
and f.datetime between d.date_from and d.date_to)
where not exists
(select 1 FROM duplicities du
where f.id=du.id)
)
, overlaps
as
(
select DISTINCT op.id_a as id, 1 as isoverlap
, STUFF((select distinct ' - ' +aa.srcFile_b
from overlaps_pairs aa
where aa.id_a = op.id_a
or op.id_b=aa.id_a
for xml path ('')),1,3,'') as overlapfiles
from overlaps_pairs op
)
select a.id, a.value, a.date, a.time, a.duration, a.srcFile,
case when d.id is not null then 1 else 0 end as isDup, d.dupFile,
o.isoverlap, o.overlapfiles
from rows a LEFT OUTER JOIN overlaps o
on (a.id=o.id)
LEFT OUTER JOIN duplicities d
on (a.id=d.id);Stack Overflow用户
发布于 2015-01-28 03:35:43
我觉得我被卷入了在可能不需要的地方使用CTE。我不知道你的用例，但是这个桌子的结构很奇怪，这是作业吗？无论如何，这样做都有好处：
WITH
tFullCTE AS (
SELECT ID as ID, Value as Value, [Date] as Date, [Time] as Time, CAST(CAST([Date] AS VARCHAR(10)) + ' ' + CAST([Time] AS VARCHAR(16)) AS DateTime2) as DateTime, Duration as Duration, srcFile as srcFile
FROM #TempTable TT
)
,dupCTE AS (
SELECT main.ID as FirstID, dups.ID as SecondID
FROM tFullCTE main
INNER join tFullCTE dups on main.value = dups.value and main.DateTime = dups.DateTime and main.id <> dups.id
)
,
overlapCTE AS (
SELECT main.ID as FirstID, ovlp.ID as SecondID
FROM tFullCTE main
INNER JOIN tFullCTE ovlp
ON main.Value = ovlp.Value
AND ((ovlp.DateTime > main.DateTime AND ovlp.DateTime < DATEADD(SECOND, main.Duration, main.DateTime))
OR (main.DateTime > ovlp.DateTime AND main.DateTime < DATEADD(SECOND, ovlp.Duration, ovlp.DateTime)))
AND main.ID <> ovlp.ID
)
SELECT main.*,
CASE WHEN EXISTS (SELECT dup.SecondID FROM dupCTE dup WHERE dup.FirstID = main.ID) THEN 1 ELSE 0 END as isDup,
STUFF(( SELECT DISTINCT ' - ' + dupDetails.srcFile
FROM dupCTE dup
INNER JOIN tFullCTE dupDetails on dup.SecondID = dupDetails.ID
WHERE dup.FirstID = main.ID
FOR XML PATH('')), 1, 3, '') as dupFile,
CASE WHEN EXISTS (SELECT ovlp.SecondID FROM overlapCTE ovlp WHERE ovlp.FirstID = main.ID) THEN 1 ELSE 0 END as isOverlap,
STUFF(( SELECT DISTINCT ' - ' + ovlpDetails.srcFile
FROM overlapCTE ovlp
INNER JOIN tFullCTE ovlpDetails on ovlp.SecondID = ovlpDetails.ID
WHERE ovlp.FirstID = main.ID
FOR XML PATH('')), 1, 3, '') as overlapFile
FROM tFullCTE main页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/28050263
复制相关文章
相似问题
腾讯云开发者
