Mysql subquery IN USES the LIMIT application example

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

The requirements are as follows. I have three forms, infor information form, mconfig material configuration form and maaply material application form. The requirement is to read out who in the application form applies for which materials

So I wrote this first:

 
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 
) 

The result is wrong

I didn't notice what error was reported at that time, but I saw something wrong with LIMIT, so I changed the code

 
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> 

This did not make an error, Mo thought OK, but after running, found that the data problem

It was only after reading out the content of the application form that I found that the position of LIMIT was wrong, so I sent LIMIT to IN again. The result is as follows

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

On closer inspection, IN does not support LIMIT. So what do you do?

Baidu then learned that it would use another temporary form in IN to find out the required contents first.

The modified 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 0,10 ) AS `tp` 
) 

After running, the problem is solved