参照以下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 ONLYDROP TABLE TBL
如果是sql2000,只能2次top来倒腾
--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
先取出最小ID,再查询大于此ID的数据 select a.* from tb a, (select min(id) mid from tb ) b where a.id > b.mid
加个where条件不就行了??? select * from tb where iID<>(select Min(ID) from tb) order by ID
(
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 ONLYDROP TABLE TBL
--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
select a.* from tb a, (select min(id) mid from tb ) b where a.id > b.mid
select * from tb where iID<>(select Min(ID) from tb) order by ID