1. Oracle数据库
SELECT * FROM TABLENAME WHERE ROWNUM <= N
2. Infomix数据库
SELECT FIRST N * FROM TABLENAME
3. DB2数据库
SELECT *
FROM (SELECT * ROW_NUMBER() OVER({ORDER BY COL1 DESC}) AS ROWNUM FROM TABLENAME)
WHERE ROWNUM <= N
或者
SELECT COLUMN FROM TABLENAME FETCH FIRST N ROWS ONLY
4. SQL Server数据库
SELECT TOP N * FROM TABLENAME
5. Sybase数据库
SET ROWCOUNT N
GO
SELECT * FROM TABLENAME
6. MySQL数据库
SELECT * FROM TABLENAME LIMIT N
7. FoxPro数据库
SELECT * TOP N FROM TABLENAME ORDER BY COLUMN
以下示例从表 [tableName] 中读取符合查询条件的前10条记录的SQL语句
1.Access
select top (10) * from [tableName] where [query condition]
1.1 带order by的查询限制
Access中对select top的语句支持有限,如果要在查询top语句的后面使用order by,则order by排序字段必须是无重复值,如果有重复值的话,那么这个TOP很可能会失效,会返回所有记录。
解决办法:在order by 最后面加入主键id,如:
select top 10 from [tableName] order by 排序字段1,id
1.2 带子查询的示例
假如id是表[tableName]的主键,以下语句期望返回三条记录,但结果返回4条记录
select top 3 * from [tableName] where id in(是个子查询,结果比如为1,2,3,4)
解决办法
select top 3 * from [tableName] where id in(是个子查询,结果比如为1,2,3,4) order by id
2 DB2
select column from [tableName] where [query condition] fetch first 10 rows only
3 MySQL
select * from [tableName] where [query condition] limit 10
4 SQL Server
4.1 读取前10条
select top (10) * from [tableName] where [query condition]
4.2 读取后10条
select top (10) * from [tableName] order by id desc
4.3 按照某个排序,第5到10这几个记录
select top 6 * from [tableName] where id not in(select top 4 id from [tableName])
5 Oracle
select * from [tableName] where rownum<=10</pre>