多层limit查询 mysql出现异常This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

最新推荐文章于 2022-07-06 15:00:07 发布

原创最新推荐文章于 2022-07-06 15:00:07 发布 · 715 阅读

0 ·

CC 4.0 BY-SA版权

数据库技术专栏收录该内容

7 篇文章

订阅专栏

本文探讨了在MySQL中如何正确使用LIMIT结合子查询的方法，解决了因语法限制导致的查询错误，并给出了修正后的SQL语句示例。

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

这个版本的MySQL还不支持的限制/所有/任何/查询”

原始的sql如下：

select  * from 
(select   pricerecord.* from pricerecord    where 1=1  and zt=0 and 
pname in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,66,67)   
order by pdate desc limit 30) as c
where pid not in  
( select  pricerecord.pid pid from pricerecord  where 1=1  and zt=0 and pname in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,66,67)    
order by pdate desc limit 1 ) order by pdate desc limit 30

这段代码可以简化一下，大概是

select  * from 
(select   t1.* from pricerecord t1   where 1=1  and zt=0 and 
pname in (1,2)   order by pdate desc limit 30) as c
where pid not in  
( select  pricerecord.pid pid from pricerecord  where 1=1  and zt=0 and pname in (1,2)    
order by pdate desc limit 1 ) order by pdate desc limit 30

这样，其实就是查询一个表的所有字段，其中，表是通过第一个查询语句查询出来的，取别名叫做c，条件是pid not in查询的not in集合是一个查询条件，同样有limit，此时，两个子句单独使用查询都没有问题，但是一起执行出现以上错误，参考此篇文章可以看到，其实他是在第二个条件语句上再加上了一层查询

其实上述语句更改成如下更易看出问题

select * from tablename where pid not (select pricerecord.pid from pricerecord where 1=1 and zt=0 and pname in (1,2) order by pdate desc limit 1) order by pdate desc limit 30

应该更改为：

select * from tablename where pid not(select temp.pid from (select pricerecord.pid pid from pricerecord where 1=1 and zt=0 and pname in (1,2) order by pdate desc limit 1)as temp) order by pdate desc limit 30

变种为

select  * from 
(select   pricerecord.* from pricerecord    where 1=1  and zt=0 and 
pname in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,66,67)   
order by pdate desc limit 30) as c  where pid not (select temp.pid from (select pricerecord.pid pid from pricerecord where 1=1 and zt=0 and pname in (1,2) order by pdate desc limit 1)as temp) order by pdate desc limit 30