代码之家 › 专栏 › 技术社区 › Saef Myth

MySQL还不支持LIMIT&IN/ALL/ANY/SOME

limit subquery mysql

Saef Myth · 技术社区 · 7 年前

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

我收到这个错误

子查询'

我如何对子查询使用limit,我可以将其转换为join语句吗?从理论上讲,这个查询在+百万的代码上运行,性能好吗?

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

1 回复 | 直到 7 年前

Himanshu Bisht 7 年前

你可以通过

`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`

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

推荐文章

Badal Solanki · 如何在MySQL中对字符串进行算术运算?

1 年前

Saleh Mehdiyev · 我想重构我的Laravel代码以遵守SOLID原则

1 年前

hello_programmers · Mysql从其他表输出一列

1 年前

Community wiki · 这个MySQL语句出了什么问题?

1 年前

Community wiki · 优化从同一表中提取的多列的查询

1 年前