blocks|key|93359|text|子查询生成传递给外部查询的结果集(虚拟表)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|93360|除非您在ORDER+BY子句中完全指定顺序，否则所有SQL查询都会按照不可预知的顺序生成结果。|offset|length|style|CODE|93361|不可预知就像随机的，除了更糟。随机意味着每次运行查询时都会得到不同的顺序。不可预测意味着每次你都会得到相同的订单，直到你没有。|93362|MySQL通常在子查询中忽略ORDER+BY子句(有几个例外，主要与子查询LIMIT子句相关)。将ORDER+BY移动到顶级查询。|93363|编辑.您还误用了MySQL对GROUP+BY的臭名昭著的非标准扩展。|BOLD|93364|entityMap^0|0|4|8|0|0|E|8|11|5|1D|8|0|0|2|E|8|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@$D|S|E|T|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|U|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|V|8|@$D|W|E|X|F|G]|$D|Y|E|Z|F|G]|$D|10|E|11|F|G]]|9|@]|A|$]]|$1|L|3|M|5|6|7|12|8|@$D|13|E|14|F|N]|$D|15|E|16|F|G]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|P|$]]

Your subquery generates a result set (a virtual table) that's passed to your outer query. 

All SQL queries, without exception, generate their results in unpredictable order unless you specify the order completely in an <code>ORDER BY</code> clause.

Unpredictable is like random, except worse. Random implies you'll get a different order every time you run the query. Unpredictable means you'll get the same order every time, until you don't.

MySQL ordinarily ignores <code>ORDER BY</code> clauses in subqueries (there are a few exceptions, mostly related to subquery <code>LIMIT</code> clauses). Move your <code>ORDER BY</code> to the top level query.

Edit. You are also misusing MySQL's notorious nonstandard extension to <code>GROUP BY</code>.

blocks|key|93884|text|你说的是给定的externalid和land。因此，不需要按externalid分组；只使用一个where子句。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|93885|与ORDER+BY和LIMIT相结合，您将得到所需的记录(即，如果存在这样的记录，则使用所需的语言，否则另一种语言)。|93886|select+*
from+mytable
where+externalid+=+10
order+by+lang+=+'fr'+desc
limit+1;|code-block|syntax|javascript|93887|更新：+Okay，根据您的评论，您希望每externalid获得“最佳”记录。在标准SQL中，您将为此使用ROW_NUMBER。其他DBMS还有更多的解决方案，例如甲骨文的KEEP+FIRST或Postgre的DISTINCT+ON。MySQL不支持任何这些。一种方法是使用变量来模拟ROW_NUMBER。另一种方法是将上面的查询用作每个externalid的子查询，以找到最佳记录：|BOLD|93888|select+*
from+mytable
where+id+in
(
++select
++++(
++++++select+m.id
++++++from+mytable+m
++++++where+m.externalid+=+e.externalid
++++++order+by+m.lang+=+'fr'+desc
++++++limit+1
++++)+as+best_id
++from+(select+distinct+externalid+from+mytable)+e
);|93889|entityMap^0|7|A|I|4|U|A|1C|5|0|1|8|A|5|0|0|0|3|K|A|1H|A|2E|A|2X|B|3Y|A|4P|A|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@$9|U|A|V|B|C]|$9|W|A|X|B|C]|$9|Y|A|Z|B|C]|$9|10|A|11|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|12|8|@$9|13|A|14|B|C]|$9|15|A|16|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|17|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|18|8|@$9|19|A|1A|B|O]|$9|1B|A|1C|B|C]|$9|1D|A|1E|B|C]|$9|1F|A|1G|B|C]|$9|1H|A|1I|B|C]|$9|1J|A|1K|B|C]|$9|1L|A|1M|B|C]]|D|@]|E|$]]|$1|P|3|Q|5|J|7|1N|8|@]|D|@]|E|$K|L]]|$1|R|3|-4|5|6|7|1O|8|@]|D|@]|E|$]]]|S|$]]

You are talking of given <code>externalid</code> and <code>land</code>. No need to group by <code>externalid</code> hence; use a mere <code>where</code> clause instead.

Combined with <code>ORDER BY</code> and <code>LIMIT</code> you get the record you want (i.e. the desired language if such a record exists, else another one).

<pre><code>select *
from mytable
where externalid = 10
order by lang = 'fr' desc
limit 1;
</code></pre>

UPDATE: Okay, according to your comment you want to get the "best" record per <code>externalid</code>. In standard SQL you'd use <code>ROW_NUMBER</code> for this. Other DBMS have further solutions, e.g. Oracle's <code>KEEP FIRST</code> or Postgre's <code>DISTINCT ON</code>. MySQL doesn't support any of these. One way would be to emulate <code>ROW_NUMBER</code> with variables. Another would be to use above query as a subquery per <code>externalid</code> to find the best records:

<pre><code>select *
from mytable
where id in
(
 select
 (
 select m.id
 from mytable m
 where m.externalid = e.externalid
 order by m.lang = 'fr' desc
 limit 1
 ) as best_id
 from (select distinct externalid from mytable) e
);
</code></pre>

I have a table x :

id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;lang&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;externalid

1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;nl&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10

2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;nl&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;11

3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;fr&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10

From this table I want al the rows for a certain lang and externalid, if the externalid doesn't exist for this lang, I want the row with any other lang. 

The subquery sorts the table correct, but when I add the group by, the sort of the subquery is lost. This works in older mysql versions but not in 5.7.

<pre><code>(
 SELECT
 *
 FROM
 x
 ORDER BY FIELD(lang, "fr") DESC, id
)
 as y
group by externalid
</code></pre>

I want the query to return the records with id 2 &amp; 3. So for each distinct external id, if possible the lang = 'fr', else any other lang.

How can i solve this problem?

MySQL ORDER BY from subquery lost by GROUP BY

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一张桌子：_d_d_123从这个表中，我希望得到特定lang的行和外部if，如果这个lang不存在外部if，我想要与任何其他lang一起的行。子查询对表进行正确排序，但当我添加组by时，子查询的排序将丢失。这在较早的mysql版本中有效，但在5.7版本中不起作用。( SELECT  * FROM  x ORDER ...

问从子查询中按组丢失的MySQL订单
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从子查询中按组丢失的MySQL订单EN