SQL常用-TOP

TOP这个命令用于取出前多少条记录，后面跟着一个数字，代表多少条记录。

示例如下：

SELECT TOP 100 * FROM YourTableName WHERE Deleted = 0 ORDER BY Id DESC;

这个TOP命令呢，适用SQL Server、Sysbase数据库，但不适用于Oracle和MySQL。

MySQL中如下语句可以实现同样的TOP功能，用到了LIMIT命令：

SELECT * FROM YourTableName WHERE Deleted = 0 ORDER BY Id DESC LIMIT 0,100;

Oracle中是另一个实现方式，用到了ROWNUM命令：

SELECT * FROM YourTableName WHERE Deleted = 0 AND ROWNUM <=100 ORDER BY Id DESC;

或者使用ORACLE中更专业的ROW_NUMBER() OVER ( PARTITION BY column1 ORDER BY column2 ) ：

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY Id DESC) AS RN FROM YourTableName WHERE Deleted = 0) WHERE RN BETWEEN 0 AND 100;