SQL查询按时间对项目进行分组，但只有在彼此接近的情况下？

Question

我正在尝试制作一条SQL语句，以便从数据库中提取样本值。该表包含与CNC机床中的刀具更改相关的值。当前语句I有正确的拉取值，但仅当工具在给定程序中出现一次时。如果该工具多次出现，则时间值从第一次加载到最后一次加载。只有一个时间列，通过查找第一个和最后一个出现的时间列，我可以确定工具的进出时间。

基本示例：

Raw Data:
Tool_Number    TIME    
100            12:00
100            12:01
100            12:02
100            12:03

Current Query Returns: 
Tool_Number    TIME_IN     TIME_OUT    
100            12:00       12:03

但是，当这个工具多次出现时，事情就会变得复杂起来，因为我不能再利用TOP和DISTINCT规则。

Raw Data:
Tool_Number    TIME    
100            12:00
100            12:01
100            12:02
100            12:03
200            12:04
200            12:05
100            12:06
100            12:07

Current Query Returns: 
Tool_Number    TIME_IN     TIME_OUT    
100            12:00       12:07
200            12:04       12:05

Ideal Query Returns:
Tool_Number    TIME_IN     TIME_OUT    
100            12:00       12:03
200            12:04       12:05
100            12:06       12:07

我们正在做时间分析，当然这严重扰乱了总时间值。当前查询为：

SELECT * FROM (SELECT DISTINCT SPINDLE_POT FROM TBL_SPINDLE_DATA_M1 
WHERE TIME BETWEEN '4/3/20131:24:13 PM' AND '4/3/2013 3:07:33 PM') AS A 

CROSS APPLY

((SELECT TOP 1 TIME FROM TBL_SPINDLE_DATA_M1 B WHERE B.SPINDLE_POT = A.SPINDLE_POT AND
TIME BETWEEN '4/3/2013 1:24:13 PM' AND '4/3/2013 3:07:33 PM') AS NEWTABLE1

JOIN

(SELECT TOP 1 TIME FROM TBL_SPINDLE_DATA_M1 B WHERE B.SPINDLE_POT = A.SPINDLE_POT 
AND TIME BETWEEN '4/3/2013 1:24:13 PM' AND '4/3/2013 3:07:33 PM' ORDER BY TIME DESC) 
AS NEWTABLE2 ON (0=0))

我绝对不是任何SQL查询专家！上面的查询可能大错特错，但它确实返回了我需要的东西。有没有办法对相似的项目进行分组，但如果它们的索引不相互接触，那么是否足够公正地不对它们进行分组？

Answer 1

下面是使用LAG/LEAD的另一种方法

DECLARE @rawdata TABLE(Tool_Number INT, [Time] TIME(0));

INSERT @rawdata VALUES
(100,'12:00'), (100,'12:01'), (100,'12:02'), (100,'12:03'),
(200,'12:04'), (200,'12:05'),
(100,'12:06'), (100,'12:07');

;WITH x AS
(
  SELECT Tool_Number, [Time], 
    s = CASE Tool_number WHEN LAG(Tool_number,1) OVER (ORDER BY [Time]) 
        THEN 0 ELSE 1 END,
    e = CASE Tool_number WHEN LEAD(Tool_number,1) OVER (ORDER BY [Time]) 
        THEN 0 ELSE 1 END
  FROM @rawdata
),
y AS 
(
  SELECT Tool_Number, s, [Time], e = LEAD([Time],1) OVER (ORDER BY [Time]) 
  FROM x WHERE 1 IN (s,e)
)
SELECT Tool_number, TIME_IN = [Time], TIME_OUT = e 
FROM y 
WHERE s = 1
ORDER BY TIME_IN;

结果：

Tool_number  TIME_IN   TIME_OUT
-----------  --------  --------
100          12:00:00  12:03:00
200          12:04:00  12:05:00
100          12:06:00  12:07:00

Answer 2

这就是所谓的“岛屿问题”，我认为这是一种解决方案(来源: Itzik Ben Gan)

select  tool_number,
        min(time) 'in',
        max(time) 'out',
        count(*)
from    (
    select  tool_number,
            time,
            ROW_NUMBER() OVER (ORDER BY time) - ROW_NUMBER() OVER (PARTITION BY Tool_Number ORDER BY time) AS Grp
    from    #temp
    ) as a
group by grp, tool_number
order by min(time)