SQL:返回其val=min(exp)的ID

Question

给定一个表，比如

pkg#，时间

0，20

1，23

2，34

3，35

4，59

我想知道哪个pkg#与它的后继pkg有最大/最小时间差(两个连续pkg之间的间隔)

在本例中，pkg-2具有最小时间差(1)，pkg-3具有最大时间差(14)。对于下一个pkg的最小/最大时间差，可以返回pkg#的sql是什么？

Answer 1

如果您使用的是SQL SERVER 2012或更高版本，您可以在此处尝试LEAD函数，以获取要在当前行中对齐的下一行的值：

SELECT *, LEAD([time]) OVER(ORDER BY [pkg#]) as nexttime
FROM [your_table]

将会产生类似这样的结果：

pkg time    nexttime
0   20  23
1   23  34
2   34  35
3   35  59
4   59  NULL

现在，比较这两列的值应该会得到您想要的结果。(注意，最后一行将具有nexttime = NULL，因为没有更多的行可以从中获取值，所以在查询时只需将其过滤掉)。

假设新表名为new_table，以获取最大差异：

select top 1 *, nexttime-time as diff
from new_table
where nexttime is not null
order by (nexttime-time) desc

要获得最小差异，只需使用order by nexttime-time

Answer 2

对@xbb的回答稍有曲解：

CREATE TABLE #t ( Pkg INT, Time INT );
INSERT  #t ( Pkg, Time )
VALUES  ( 0, 20 ),
       ( 1, 23 ),
       ( 2, 34 ),
       ( 3, 35 ),
       ( 4, 59 );

SELECT  Pkg
,      Time
,      Time - LAG(Time) OVER ( ORDER BY Pkg ) AS TimeSincePrevious
,      ABS(time - LEAD(Time) OVER ( ORDER BY Pkg )) AS TimeUntilNext
FROM       #t;

DROP TABLE #t;

将产生以下结果：

Pkg  Time  TimeSincePrevious  TimeUntilNext
0    20    NULL               3
1    23    3                  11
2    34    11                 1
3    35    1                  24
4    59    24                 NULL

Answer 3

请看下面的解决方案-我将查询分解为三个步骤：

WITH Ordered AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY pkg) rowNum, pkg, [time] FROM Test
),
Diffs AS
(
    SELECT T1.pkg,
           T2.[time]-T1.[time] diff,
           MIN(T2.[time]-T1.[time]) OVER () minimum,
           MAX(T2.[time]-T1.[time]) OVER () maximum
    FROM Ordered T1
    JOIN Ordered T2 ON T1.rowNum = T2.rowNum-1
)
SELECT pkg, diff FROM Diffs
WHERE diff=minimum OR diff=maximum
ORDER by diff

使用偏移量1的

2. Join的行数，计算差异、最小和最大
3. 筛选行不等于最小或最大

如果出现平局，查询可能会返回更多行。可以通过将最终选择替换为以下内容来简单地删除关联：

...
SELECT MIN(pkg) pkg, diff FROM Diffs
WHERE diff=minimum OR diff=maximum
GROUP BY diff
ORDER by diff