已知数据库表名为users,表字段如下图:
教程中使用的数据库是sqlserver,取出前几条记录的sql语句是:
select top N * from users where userID not in (selecttop N userID from users);
以取出前3条记录为例,sql语句是:
select top 3 * from users where userID not in (select top 0 userID from users);
也即排除前0条的记录再取出前3条。
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
在使用MySQL数据库做同样的操作时会报错:错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3 * from users where userID not in (select top 0 userID from users)' at line 1
原因是相同的功能,SQL Server和MySQL使用的SQL语句不同。
MySQL下的sql语句为:
SELECT * FROM users LIMIT M,N;或者
SELECT * FROM users WHERE userID NOT IN (SELECT t.userID FROM (SELECT * FROM users LIMIT M ) AS t) LIMIT N;
以取出前3条记录为例,mysql语句是:
SELECT * FROM users LIMIT 0,3;或者
SELECT * FROM users WHERE userID NOT IN (SELECT t.userID FROM (SELECT * FROM users LIMIT 0 ) AS t) LIMIT 3; 结果如图所示: