我有一张桌子
T (variable_name,start_no,end_no)
具有如下值的值：
(x,10,20) (x,30,50) (x,60,70) (y,1,3) (y,7,8)
所有间隔都保证不相交.
(x,21,29) (x,51,59) (y,4,6)
我可以不用光标吗？
我正在考虑通过variable_name进行分区,然后按start_no进行排序.但接下来该怎么办呢？给定行集中的当前行,如何访问“下一行”？
解决方法
由于您没有指定哪个版本的sql Server,我有多个解决方案.如果你还在摇晃sql Server 2005,那么Giorgi’s很好地使用了CROSS APPLY.
注意：对于这两种解决方案,我使用where子句来过滤掉不正确的值,因此即使数据错误且行重叠,它也会忽略这些值.
我的桌子版本
DECLARE @T TABLE (variable_name CHAR,start_no INT,end_no INT)
INSERT INTO @T
VALUES ('x',20),('x',50),70),('y',3),8);
SELECT *
FROM
(
SELECT variable_name,LAG(end_no,1) OVER (PARTITION BY variable_name ORDER BY start_no) + 1 AS start_range,start_no - 1 AS end_range
FROM @T
) A
WHERE end_range > start_range
WITH CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY variable_name ORDER BY start_no) row_num,*
FROM @T
)
SELECT A.variable_name,B.end_no + 1 AS start_range,A.start_no - 1 AS end_range
FROM CTE AS A
INNER JOIN CTE AS B
ON A.variable_name = B.variable_name
AND A.row_num = B.row_num + 1
WHERE A.start_no - 1 /*end_range*/ > B.end_no + 1 /*start_range*/
