多层limit查询 mysql出现异常This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
来源:互联网 发布:淘宝联盟分享赚 实操 编辑:程序博客网 时间:2024/06/01 08:48
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 cwhere 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 cwhere 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
阅读全文
1 0
- 多层limit查询 mysql出现异常This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
- mysql error This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery
- This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery
- This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery
- This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery
- This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery
- 1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
- This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
- This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery
- This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery
- [Err] 1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
- This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
- This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery
- This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' 错误解决
- Mysql:This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' 错误解决
- mysql子查询用limit报错( “This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’ ”)
- SQL Error (1235): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery
- Error Code: 1235. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
- Python装饰器用法详解
- java小算法—将阿拉伯字母转为汉字
- 呵呵
- Java集合——HashMap(四)扩容机制——resize()
- linux文件重定向问题
- 多层limit查询 mysql出现异常This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
- python-property
- BZOJ 3942: [Usaco2015 Feb]Censoring KMP
- ubuntu安装python3.5并改默认python为3.5
- shared_ptr智能指针源码剖析
- Docker windows安装及遇到的坑爹问题
- windows系统用anaconda配置安装TensorFlow (2017.4.13)tensorflow支持python3.5版本 1.打开anaconda依次输入: 1 conda con
- 解决Ubuntu16.04网络图标启用wifi不见的问题
- Python中继承、多态、多继承、判断类型、json.load()解析、获取/设置对象信息