带左连接的 MySQL 联合查询 - 顺序错误？
我们有一个联合查询。这是一个基本（类似）示例：
SELECT a.Name, b.Info
FROM a
LEFT JOIN b ON (a.ID = b.ID)
WHERE a.Name LIKE "a%"
UNION
SELECT a.Name, b.Info
FROM a
LEFT JOIN b ON (a.ID = b.ID)
WHERE a.Name LIKE "b%"
ORDER BY a.Name, b.Info;
我收到一条错误，提示“‘order Clause’中存在未知列‘b.Info’”。
当我从 ORDER BY 子句末尾删除“b.Info”时，它起作用了。
想法？
We have a Union Query. Here's a basic (similar) example:
SELECT a.Name, b.Info
FROM a
LEFT JOIN b ON (a.ID = b.ID)
WHERE a.Name LIKE "a%"
UNION
SELECT a.Name, b.Info
FROM a
LEFT JOIN b ON (a.ID = b.ID)
WHERE a.Name LIKE "b%"
ORDER BY a.Name, b.Info;
I am receiving an error that says "Unknown column 'b.Info' in 'order clause'".
When I remove the "b.Info" from the end of the ORDER BY clause, it works.
Ideas ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
合并发生后，Orderby 将应用于合并的结果集。此时，可以说只有一张表，因此对 b.Info 的引用将无效。
请注意，这可能会非常慢（对于较大的结果集），因为您强制 MySQL 使用临时表进行排序操作。
The Orderby is being applied to the combined result-set - after the Union has taken place. At this point, there is only one table so to speak, so the reference to b.Info will be invalid.
Be aware that this could be potentially very slow (with large result sets), as you are forcing MySQL to use a temporary table for the sorting operation.
MySQL 文档（12.2.8.3 UNION 语法）中描述了此问题。您不能使用原始表名，因此请为每一列指定一个别名，并在 ORDER BY 子句中使用该别名：
要使用 ORDER BY 或 LIMIT 子句对整个 UNION 结果进行排序或限制，将各个 SELECT 语句放在括号内，并放置 ORDER BY 或 LIMIT 在最后一个之后。以下示例使用这两个子句：
这种 ORDER BY 不能使用包含表名（即 tbl_name.col_name 格式的名称）的列引用。相反，请在第一个 SELECT 语句中提供列别名，并在 ORDER BY 中引用该别名。
This problem is described in the MySQL documentation (12.2.8.3 UNION Syntax). You can't use the original table name, so give each column an alias and use this one in the ORDER BY clause:
To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one. The following example uses both clauses:
This kind of ORDER BY cannot use column references that include a table name (that is, names in tbl_name.col_name format). Instead, provide a column alias in the first SELECT statement and refer to the alias in the ORDER BY.
你为什么要使用 union？这个查询是相同的并且更快：
阅读括号
Why are you using union at all? this query is the same and faster:
Read up on parentheses
我认为当您执行 UNION 查询时，您应该指定列位置，而不是 ORDER BY 子句中的名称。尝试
ORDER BY 1,2。I think when you're doing
UNIONquery you should specify column position, not name in theORDER BYclause. TryORDER BY 1,2.