替代在MYSQL中的SubQuery中使用LIMIT关键字 [英] Alternative to using LIMIT keyword in a SubQuery in MYSQL

问题描述

我有一个表TEST，包含以下列：
code_ver（VARCHAR）
suite（VARCHAR）
date（DATE）

现在我想选择10行，有不同的值code_ver&因此我写了以下查询：

<$ p

$ p> select *
from test
其中code_ver IN（select DISTINCT code_ver
from test
其中code_ver NOT LIKE'％DevBld％'
ORDER by date DESC LIMIT 10）;

这个查询应该是理想的工作，但我的MySQL版本说：这个版本的MySQL还没有支持LIMIT& IN / ALL / ANY / SOME子查询

有人可以建议我替换此查询吗？

解决方案

你得到的错误并不完全是因为MySQL的版本。我认为所有版本支持。您必须更改LIMIT 10位置，并将其放在）之后。让我知道如果它为你工作。

  > SELECT * FROM test where name IN（
 SELECT DISTINCT name 
 FROM projects 
 WHERE name NOT LIKE％DevBld％
 ORDER by date_created DESC 
）LIMIT 10 ; 
  

更新：请尝试以下方法，以这种方式工作：

  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; 
  

I have a table TEST with the following columns : 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.

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 : This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Can someone suggest me an alternative to this query?

解决方案

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.

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;

这篇关于替代在MYSQL中的SubQuery中使用LIMIT关键字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！