Mysql子查询中不能使用limit或者in

最新推荐文章于 2021-02-27 06:32:22 发布

蚊子爱喝水

最新推荐文章于 2021-02-27 06:32:22 发布

阅读量846

点赞数 1

分类专栏： MySQL

本文链接：https://blog.csdn.net/liuxl57805678/article/details/111175887

版权

MySQL 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

SQl语句

SELECT
	`a`.`article_id` 
FROM
	`article_tags` `a` 
WHERE
	a.tag_id IN ( SELECT id FROM tags WHERE `name` = 'tagName' AND fmid IN ( 5, 6, 7 ) LIMIT 100 ) 
ORDER BY
	a.article_id DESC 
	LIMIT 1

报错信息

SQLSTATE[42000]: Syntax error or access violation: 1235 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery

原因:

MYSQL子查询不支持limit

解决办法：

再使用一层循环

SELECT
	`a`.`article_id` 
FROM
	`article_tags` `a` 
WHERE
	a.tag_id IN (
	SELECT
		id 
	FROM
		( SELECT id FROM tags WHERE `name` = 'tagName' AND fmid IN ( 5, 6, 7 ) LIMIT 100 ) AS tid 
	) 
ORDER BY
	a.article_id DESC 
	LIMIT 1

SELECT `a`.`article_id` FROM `article_tags` `a` WHERE a.tag_id in (SELECT id from (SELECT id from tags WHERE `name`='tagName' and fmid in (5,6,7) LIMIT 100) as tid) ORDER BY a.article_id desc LIMIT 1

注意颜色表示部分，不要写错了哈