各种消灭“取31-40行数据”--取指定范围不连续id的解法和分析

　　一般面试都会出这种“取指定范围的不连续id的数据”，这个在分页是有一些意义的，不过个人觉得面试应该更多是考察“道”，而不是奇技淫巧。不管怎样，总结一下t-sql中如何操作。

step 1  先建测试数据

USE Test
DROP TABLE test
CREATE TABLE test(
 id INT,
 VALUE INT)
 
 DECLARE @id INT, @value INT
 SET @id = 1
 set @value =1

 
 WHILE (@id<300)
 BEGIN
  INSERT INTO test
  (
   id,
   [VALUE]
  )
  VALUES
  (
   @id,
   @value
   
  )
  
  SET @id = @id + 2
  SET @value = @id
  
  
 END

SELECT * FROM test

这样就有了一个150行的样本空间。

 

 

step 2 solution

method1 sql2005提供了一个新函数 rowNumber。

可以利用此函数和with一起实现：

WITH TABLEwithConID
AS
(SELECT * ,ROW_NUMBER() OVER (ORDER BY value) AS ContinuousID FROM test)

 select * FROM  TABLEwithConID where ContinuousID BETWEEN 31 AND 40

 

在更早的版本，可以考虑以下方法

 

method2  子查询

select top 10 * from test where id not in( select top 30 Id from test order by Id asc ) order by id asc;

 变种

 
  select top 10 * from test
 where id >
(SELECT MAX(id) FROM
 (SELECT TOP 30 id FROM test ORDER BY id asc) t) 
 ORDER BY id asc

此种方法不利于阅读

 

既然有in，自然有exists的版本：

 select top 10 * from test t 
WHERE NOT EXISTS 
(SELECT * from 
(SELECT TOP 30 * FROM test ORDER BY id asc) t2 
WHERE t2.id =t.id 
)

此版本效率最差，因为采用的是相关子查询：主查询每遍历一条记录时，都要针对主查询的值执行子查询。

 

--只能想到这么多，如果有其他方法请留言，我补充进去--