萨钦职业生涯中两场测试赛的最长间隔

Question

我是SQL server的新手，我在这里找到的Sachin的击球统计数据(板球)进行了练习。(Sachin Batting Statistics)。我想找出萨钦职业生涯中两场测试赛之间最长的差距。因此，基本上必须根据测试匹配来过滤它，并在Start_DateAscending列中找到最大差异？希望这能说得通。Sample table added if link doesn't make sense

编辑:我创建了一个具有不同日期的示例表。该列被命名为DateValues。现在，我想找出DateValue列中任意两个连续行之间的最大差值的代码。例如，在本例中，答案是1989年12月9日到1991年12月26日之间的2年17天

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
        DROP TABLE #mytable


CREATE TABLE #mytable 
        (
        ID    INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        DateValue  DATETIME
        )

SET DATEFORMAT DMY

SET IDENTITY_INSERT #mytable ON

INSERT INTO #mytable 
       (ID, DateValue)

SELECT '11', 'Nov 15 1989 12:00AM' UNION ALL    
SELECT '59', 'Nov 23 1989 12:00AM' UNION ALL
SELECT '37', 'Dec 09 1989 12:00AM' UNION ALL    
SELECT '44', 'Dec 26 1991 12:00AM' UNION ALL    
SELECT '55', 'May 31 1993 12:00AM' UNION ALL    
SELECT '60', 'May 15 1995 12:00AM' UNION ALL    
SELECT '57', 'Jan 12 1996 12:00AM' UNION ALL    
SELECT '43', 'Jan 19 1996 12:00AM' UNION ALL   
SELECT '49', 'Jan 31 1996 12:00AM' UNION ALL    
SELECT '18', 'Oct 17 1997 12:00AM'

这是我在这个网站上找到的解决方案，我得到的答案是1900-01-01！

SELECT MAX(#mytable.DateValue-h.DateValue) as maxDiff 
FROM #mytable
LEFT JOIN #mytable h 
ON h.ID=[dbo].#mytable.ID AND #mytable.DateValue>=h.DateValue
WHERE h.DateValue IS NOT NULL

Answer 1

如果您使用的是SQL Server 2012或更高版本，则此SQL将返回两次测试之间的最大天数间隔：

select max(datediff(day, a.TestDate, a.NextTest)) as BiggestGap
from (
    select DateValue as TestDate, lead(DateValue) over (order by DateValue) as NextTest
    from #mytable m
) a

该查询做的第一件事(在括号内)是获取一个表，该表列出了所有测试匹配和下一个测试匹配的日期。这就是最里面的查询所提供的功能:它选择所有测试日期，并使用lead函数在该测试之后立即显示匹配日期。

括号中的select数据(包括ID)如下所示：

ID          TestDate                NextTest
----------- ----------------------- -----------------------
11          1989-11-15 00:00:00.000 1989-11-23 00:00:00.000
59          1989-11-23 00:00:00.000 1989-12-09 00:00:00.000
37          1989-12-09 00:00:00.000 1991-12-26 00:00:00.000
44          1991-12-26 00:00:00.000 1993-05-31 00:00:00.000
55          1993-05-31 00:00:00.000 1995-05-15 00:00:00.000
60          1995-05-15 00:00:00.000 1996-01-12 00:00:00.000
57          1996-01-12 00:00:00.000 1996-01-19 00:00:00.000
43          1996-01-19 00:00:00.000 1996-01-31 00:00:00.000
49          1996-01-31 00:00:00.000 1997-10-17 00:00:00.000
18          1997-10-17 00:00:00.000 NULL

在这之后(在括号之外)，只需查找日期之间差异最大的行即可。在SQL Server中，最好使用datediff函数来获取两个日期之间的差值，而不是使用数学运算符，如您所看到的示例中的-，因此我们使用它来获取每行之间的天数差值。max用于获取其中的最大值，从而返回两个匹配项之间的最大差距。

使用提供的示例SQL数据，最大的差距是747天(大约2年17天)。