mysql子查询中不能使用LIMIT

最新推荐文章于 2024-08-08 08:47:57 发布

程宇寒

最新推荐文章于 2024-08-08 08:47:57 发布

阅读量5.3k

点赞数 1

文章标签： mysql LIMIT 子查询

本文链接：https://blog.csdn.net/czh500/article/details/86275400

版权

mysql子查询中不能使用LIMIT

/*
今天遇到个问题，如下：
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
*/

直接po截图和代码

我们可以通过LIMIT来删除表中的数据

第一个案例如下：

第二个案例如下：

/*
今天遇到个问题，如下：
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
*/

#删除admin表中50条记录，并且是从第3条记录开始删
#报错
DELETE FROM admin WHERE 1 = 1 LIMIT 2,50;
#报错
DELETE FROM admin LIMIT 2,50;
#会报错(子查询中是不能使用LIMIT的)
DELETE FROM admin WHERE id IN (SELECT id FROM admin LIMIT 2,50);
#删除成功(#但是在有LIMIT关键字的子查询的外面再包一层就可以了),以下sql语句正确执行
DELETE FROM admin WHERE id IN (SELECT ad.id FROM (SELECT id FROM admin LIMIT 2,50) AS ad);#记得取别名

#以下查询语句都可以正确执行
SELECT id FROM admin LIMIT 2,50;
SELECT * FROM (SELECT id FROM admin LIMIT 2,50) AS ad;
SELECT ad.* FROM (SELECT id FROM admin LIMIT 2,50) AS ad;
SELECT * FROM (SELECT id FROM admin LIMIT 2,50) ad;
SELECT ad.id FROM (SELECT id FROM admin LIMIT 2,50) AS ad;
SELECT id FROM (SELECT id FROM admin LIMIT 2,50) AS ad;
#

#子查询中是不能使用LIMIT的,以下sql语句报错
SELECT * FROM admin WHERE id IN (SELECT id FROM admin LIMIT 2,50);
#但是在有LIMIT关键字的子查询的外面再包一层就可以了,以下sql语句正确执行
SELECT * FROM admin WHERE id IN (SELECT adm.id FROM (SELECT id FROM admin LIMIT 2,50) AS adm);#记得取别名

#
CREATE TABLE admin2 SELECT * FROM admin;
#
SELECT * FROM admin2;
#
SELECT * FROM admin2 WHERE id IN
(SELECT ad.id FROM (SELECT id FROM admin2 LIMIT 2,3) AS ad);
#
SELECT * FROM admin2 WHERE id IN
(SELECT ad.id FROM (SELECT id FROM admin2 LIMIT 4,2) AS ad);
#
SELECT * FROM admin2 WHERE id IN
(SELECT ad.id FROM (SELECT id FROM admin2 LIMIT 2,3) AS ad)
OR id IN (SELECT ad.id FROM (SELECT id FROM admin2 LIMIT 4,2) AS ad);

#删除数据
DELETE FROM admin2 WHERE id IN
(SELECT ad.id FROM (SELECT id FROM admin2 LIMIT 2,3) AS ad)
OR id IN (SELECT ad.id FROM (SELECT id FROM admin2 LIMIT 4,2) AS ad);

#
SELECT * FROM admin2 WHERE id IN
(SELECT ad.id FROM (SELECT id FROM admin2 LIMIT 3,4) AS ad)
OR id IN (SELECT ad.id FROM (SELECT id FROM admin2 LIMIT 12,2) AS ad);

#删除数据
DELETE FROM admin2 WHERE id IN
(SELECT ad.id FROM (SELECT id FROM admin2 LIMIT 3,4) AS ad)
OR id IN (SELECT ad.id FROM (SELECT id FROM admin2 LIMIT 12,2) AS ad);