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