select 如何从第二个开始选

[zjroland]

ID username

1 naming
2 huxing
3 yuiming
4 zhangming
5 liping

我要按照ID的升序开始选，但要从第二个开始，请问怎么写SELECT ？

[zhll2046]

参照以下code，SQL Server 2012 新增了一个OFFSET从句，很好用。 2012以下版本只能通过row_number排好序再甄选了。

CREATE TABLE TBL
(
id INT PRIMARY KEY,
name VARCHAR(99)
);

INSERT INTO TBL 
SELECT 1 AS ID ,N'naming' AS NAME UNION ALL
SELECT 2 ,N'huxing' UNION ALL
SELECT 3 ,N'yuiming' UNION ALL         
SELECT 4 ,N'zhangming' UNION ALL 
SELECT 5 ,N'liping' ;

--2008
;WITH CTE AS
(
SELECT id,name,ROW_NUMBER() OVER(ORDER BY id) AS rownum FROM TBL
)
SELECT id,name FROM CTE where rownum>2

--2012 onwards 从哪行开始取，取几行
SELECT * FROM TBL ORDER BY ID OFFSET 1 ROWS FETCH NEXT 3 ROWS ONLY

DROP TABLE TBL

[haitao]

如果是sql2000，只能2次top来倒腾

[山寨DBA]

 --SQL 2012
 select * from TB order by ID offset 1 rows fetch next 1000000 rows only -- 注意offset 默认是从0开始算第一行的

 --SQL2005/08/08R2
 select ID,username from 
 (
	select ID,username,row_number() over (order by ID) as num from TB order by id 
 ) as t 
 where num>=2

 --SQL2000
 select * from TB where ID not in (select top 1 ID from TB order by ID) order by ID 

[ghs79]

先取出最小ID，再查询大于此ID的数据 select a.* from tb a, (select min(id) mid from tb ) b where a.id > b.mid

[mathsfan]

加个where条件不就行了？？？ select * from tb where iID<>(select Min(ID) from tb) order by ID

[---涛声依旧---]

引用 6 楼 mathsfan 的回复:

select * from tb where iID<>(select Min(ID) from tb) order by ID

+1

[还在加载中灬]

SELECT TOP 1 * FROM TB
WHERE ID>1
ORDER BY ID

是这个意思?