Example of using limit in Mysql subquery

There was a problem in the project these two days, and an error was reported after LIMIT was used.

This is the requirement. I have three tables: infor info table, mconfig material configuration table, and maaply material application form. The requirement is to read who applies for the materials in the application form.

So I wrote:
Copy codeThe Code is 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 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
)

An error is returned.

At that time, I didn't pay attention to the reported error. I only saw the LIMIT error, so I changed the code.

Copy codeThe Code is 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 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
)
<Pre name = "code" class = "SQL"> LIMIT 0, 10 </pre>

In this case, no error is reported. Mo Li thought it was OK, but after running it, the data was faulty.

Unlike reading the content of the application form, I found that the LIMIT location was wrong. So I sent the LIMIT to IN again. The result is as follows:

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

You can see it IN detail. IN does not support LIMIT. What should we do?

Then du Niang learned that she would use a temporary table IN the IN statement to find out the required content first,

The modified code is as follows:
Copy codeThe Code is 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 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) AS 'tp'
)

After running, the problem is solved ~~~