在 MYSQL 的子查询中使用 LIMIT 关键字的替代方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12810346/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Alternative to using LIMIT keyword in a SubQuery in MYSQL
提问by Pi Horse
I have a table TEST with the following columns :
我有一个包含以下列的表 TEST：
code_ver (VARCHAR)
suite (VARCHAR)
date (DATE)
Now I want to select 10 rows with a distinct value of code_ver & code_ver NOT LIKE '%DevBld%' sorted by date desc.
现在我想选择具有不同值 c 的 10 行ode_ver & code_ver NOT LIKE '%DevBld%' sorted by date desc.
So I wrote the following query:
所以我写了以下查询：
select *
from test
where code_ver IN (select DISTINCT code_ver
from test
where code_ver NOT LIKE '%DevBld%'
ORDER by date DESC LIMIT 10);
This query should ideally work, but my version of MySQL says :
理想情况下，此查询应该可以工作，但我的 MySQL 版本说：
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
此版本的 MySQL 尚不支持“LIMIT & IN/ALL/ANY/SOME 子查询”
Can someone suggest me an alternative to this query?
有人可以建议我替代此查询吗？
采纳答案by Sara
The error you are getting is not exactly because of the version of MySQL. I think all versions support that. You have to change the LIMIT 10 place and place it after ")". Let me know if it works for you. I ran the bellow one on mine and it works.
你得到的错误并不完全是因为 MySQL 的版本。我认为所有版本都支持。您必须更改 LIMIT 10 位置并将其放在“)”之后。请让我知道这对你有没有用。我在我的上运行了波纹管，它起作用了。
E.g.
例如
SELECT * FROM test where name IN (
SELECT DISTINCT name
FROM projects
WHERE name NOT LIKE "%DevBld%"
ORDER by date_created DESC
) LIMIT 10;
Update: Try the one below, this way order would work:
更新：试试下面的一个，这样顺序就可以了：
SELECT * FROM automation.e2e_projects WHERE name IN (
SELECT DISTINCT name
FROM automation.e2e_projects
WHERE name NOT LIKE "%DevBld%"
) ORDER by date_created DESC LIMIT 10;
回答by aneetkukreja
Answer suggested by Layke is wrong in my purview. Intention of using limit in subquery is so main query run on limited records fetched from subquery. And if we keep limit outside then it makes limit useless for subquery.
Layke 建议的答案在我的职权范围内是错误的。在子查询中使用限制的意图是在从子查询获取的有限记录上运行主查询。如果我们将 limit 保留在外面，那么它会使子查询的 limit 无用。
Since mysql doesn't support yet limit in subquery, instead you can use JOIN as follows:
由于 mysql 尚不支持子查询中的限制，因此您可以按如下方式使用 JOIN：
SELECT * FROM test
JOIN
(
SELECT DISTINCT code_ver
FROM test
WHERE code_ver NOT LIKE '%DevBld%'
ORDER BY date DESC LIMIT 10
) d
ON test.code_ver
IN (d.code_ver)
ORDER BY xyz;
回答by pilcrow
Put the subquery in a derived table:
将子查询放在派生表中：
SELECT test.*
FROM test
LEFT JOIN (SELECT DISTINCT code_ver
FROM mastertest
WHERE code_ver NOT LIKE '%DevBld%'
ORDER BY `date` DESC
LIMIT 10) d
USING (code_ver)
WHERE d.code_ver IS NOT NULL;
(You could also RIGHT JOIN that, of course, and drop the outer WHERE condition.)
（当然，您也可以 RIGHT JOIN 并删除外部 WHERE 条件。）
回答by yash
You can also use same query, just by adding one extra layer of select before subquery. and that's it. It will work.
您也可以使用相同的查询，只需在子查询之前添加一层额外的选择即可。就是这样。它会起作用。
select * from test
where code_ver IN (select * from (select DISTINCT code_ver
from test
where code_ver NOT LIKE '%DevBld%'
ORDER by date DESC LIMIT 10) as t1);
