Home Database Mysql Tutorial Mysql子查询IN中使用LIMIT应用示例_MySQL

Mysql子查询IN中使用LIMIT应用示例_MySQL

Jun 01, 2016 pm 01:26 PM
project

bitsCN.com 这两天项目里出了一个问题,LIMIT使用后报错。

需求是这样的,我有3张表,infor信息表,mconfig物料配置表,maaply物料申请表,要求是读出申请表中哪些人申请哪些物料

于是我先是这样写的:

SELECT infor.name,infor.phone,infor.add,
mconfig.mname,mapply.acount,from_unixtime(mapply.atime,'%Y-%m-%d') as 'atime'
FROM mapply right JOIN infor ON mapply.uid = infor.uid inner JOIN mconfig ON mapply.mid = mconfig.mid
WHERE mapply.aid
IN (
SELECT aid
FROM `mapply` where state = $state
ORDER BY `atime` , `uid` DESC
LIMIT 0,10
)

结果报错了

当时没注意报的什么错误,只是看到LIMIT什么的错误,于是改了下代码


SELECT infor.name,infor.phone,infor.add,
mconfig.mname,mapply.acount,from_unixtime(mapply.atime,'%Y-%m-%d') as 'atime'
FROM mapply right JOIN infor ON mapply.uid = infor.uid inner JOIN mconfig ON mapply.mid = mconfig.mid
WHERE mapply.aid
IN (
SELECT aid
FROM `mapply` where state = $state
ORDER BY `atime` , `uid` DESC
)

LIMIT 0,10
Copy after login


这样没有报错,莫离以为OK了,但是运行后发现,数据有问题

和单纯的读出申请表的内容不一样,才发现LIMIT的位置放错了,于是又把LIMIT发在IN里,结果报错如下

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

细看才知道,IN里不支持LIMIT。那怎么办呢?

于是度娘后得知,在IN里再使用一张临时表,把需要的内容先查出来,

修改后代码如下:

SELECT infor.name,infor.phone,infor.add,
mconfig.mname,mapply.acount,from_unixtime(mapply.atime,'%Y-%m-%d') as 'atime'
FROM mapply right JOIN infor ON mapply.uid = infor.uid inner JOIN mconfig ON mapply.mid = mconfig.mid
WHERE mapply.aid
IN (
SELECT aid
FROM (SELECT `aid` FROM `mapply` where state = $state
ORDER BY `atime` , `uid` DESC
LIMIT 0,10)AS `tp`
)

运行后,问题解决~~~bitsCN.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Can AI conquer Fermat's last theorem? Mathematician gave up 5 years of his career to turn 100 pages of proof into code Can AI conquer Fermat's last theorem? Mathematician gave up 5 years of his career to turn 100 pages of proof into code Apr 09, 2024 pm 03:20 PM

Can AI conquer Fermat's last theorem? Mathematician gave up 5 years of his career to turn 100 pages of proof into code

A closer look at PyCharm: a quick way to delete projects A closer look at PyCharm: a quick way to delete projects Feb 26, 2024 pm 04:21 PM

A closer look at PyCharm: a quick way to delete projects

Share an easy way to package PyCharm projects Share an easy way to package PyCharm projects Dec 30, 2023 am 09:34 AM

Share an easy way to package PyCharm projects

PyCharm Practical Tips: Convert Project to Executable EXE File PyCharm Practical Tips: Convert Project to Executable EXE File Feb 23, 2024 am 09:33 AM

PyCharm Practical Tips: Convert Project to Executable EXE File

How to Make a Shopping List in the iOS 17 Reminders App on iPhone How to Make a Shopping List in the iOS 17 Reminders App on iPhone Sep 21, 2023 pm 06:41 PM

How to Make a Shopping List in the iOS 17 Reminders App on iPhone

Based on the open source ChatGPT Web UI project, quickly build your own ChatGPT site Based on the open source ChatGPT Web UI project, quickly build your own ChatGPT site Apr 15, 2023 pm 07:43 PM

Based on the open source ChatGPT Web UI project, quickly build your own ChatGPT site

What to do if there is an error when starting the react project What to do if there is an error when starting the react project Dec 27, 2022 am 10:36 AM

What to do if there is an error when starting the react project

PyCharm Tutorial: How to remove items in PyCharm? PyCharm Tutorial: How to remove items in PyCharm? Feb 24, 2024 pm 05:54 PM

PyCharm Tutorial: How to remove items in PyCharm?

See all articles