的MySQL还不支持限制和IN/ALL/ANY/SOME

问题描述：

SELECT *, 
IF(users_posts.uid IN (SELECT puid FROM post_ups WHERE post_ups.uid = UID LIMIT 400) AND users_posts.uid <> UID ,10,0) 
    FROM users_posts

即时得到这个错误的MySQL还不支持限制和IN/ALL/ANY/SOME

该版本的MySQL还不支持“LIMIT & IN/ALL/ANY/SOME 子查询”

如何使用子查询上的限制，我可以将它转换为连接语句，是表现这个查询好上百万+这个重新编码，理论上戈纳运行？

UPDATE 这段代码的结果是否与上面相同？

SELECT *,IF(ups.puid = t1.uid,10,0) 
    FROM users_posts t1 
    LEFT JOIN (SELECT puid FROM post_ups WHERE post_ups.uid = 135 ORDER BY `created_date` DESC LIMIT 400) AS ups ON ups.puid = t1.uid AND t1.uid <> 135

见https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very - 简单-SQL查询 – Strawberry

答

您可以通过

`SELECT *, 
IF(users_posts.uid IN (select * from (SELECT puid FROM post_ups WHERE post_ups.uid = UID LIMIT 400) tmp_tbl) AND users_posts.uid <> UID ,10,0) 
    FROM users_posts`

做到这一点或替代LEFT JOIN的使用INNER JOIN。

SELECT *,IF(ups.puid = t1.uid,10,0) 
FROM users_posts t1 INNER JOIN (SELECT puid FROM post_ups WHERE post_ups.uid = 135 ORDER BY `created_date` DESC LIMIT 400) AS ups ON ups.puid = t1.uid AND t1.uid <> 135

的MySQL还不支持限制和IN/ALL/ANY/SOME

相关推荐