Using the Limit application example in Mysql subquery _mysql

Source: Internet
Author: User
This two-day project has a problem, limit use after the error.

The demand is like this, I have 3 tables, infor information table, mconfig material configuration table, maaply material request form, request to read out which person in the application form request which material

So I wrote this first:
Copy Code code as follows:

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 to 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
)

The result is an error.

At that time did not pay attention to the report of what error, just see limit what error, so changed the code

Copy Code code as follows:

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 to 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
)
<pre name= "code" class= "SQL" >limit 0,10</pre>

So no error, MO away thought OK, but after running found that the data have problems

and simply read out the content of the application form is not the same, only to find limit position misplaced, and then put limit in, the results of the following error

This version of the MySQL doesn ' t yet support ' LIMIT & in/all/any/some subquery '

Look carefully to know, in does not support limit. What about it?

So the Niang later learned that in the use of a temporary table, the need to find out the contents of the first,

The modified code is as follows:
Copy Code code as follows:

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 to Mapply.uid = Infor.uid inner JOIN mconfig on mapply.mid = Mconfig.mid
WHERE Mapply.aid
In (
SELECT Aid
From (SELECT ' aid ' to ' mapply ' where state = $state
ORDER BY ' atime ', ' uid ' DESC
LIMIT 0,10) as ' TP '
)

After the operation, solve the problem ~ ~ ~
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.