查询坚持“Order By”中的列；子句匹配“Group by”中的列条款
这个有点奇怪。我正在尝试运行以下连接 3 个表的查询。
Select t3.id, t3.name, t3.phone_no, t1.reg_dtm, count(t1.reg_dtm)
from tableA t1, tableB t2, tableC t3
Where t1.id = t2.id
And t2.id = t3.id
Group by t3.id, t3.name, t3.phone_no, t1.reg_dtm
Order by t2.id, t1.reg_dtm
上面的查询返回以下错误
ORA-00979: not a GROUP BY expression
，但是如果我更改它以使 group by 子句中的所有内容都在 order by 子句中，那么它就可以工作。
Select t3.id, t3.name, t3.phone_no, t1.reg_dtm, count(t1.reg_dtm)
from tableA t1, tableB t2, tableC t3
Where t1.id = t2.id
And t2.id = t3.id
Group by t3.id, t3.name, t3.phone_no, t1.reg_dtm
Order by t3.id, t3.name, t3.phone_no, t1.reg_dtm
这究竟是什么原因呢？
我认为问题可能是因为第一个查询的 order by 语句中显示的 t2.id 不是 group by 语句的一部分。如果这是原因，那么为什么它很重要呢？我以前从未经历过这种情况，并且不认为 group by 和 order by 语句之间有任何关系。
我在 Oracle 10G 和 MySQL 上测试了以上内容。
提前致谢
A bit of an odd one this one. I am trying to run the following query joining 3 tables.
Select t3.id, t3.name, t3.phone_no, t1.reg_dtm, count(t1.reg_dtm)
from tableA t1, tableB t2, tableC t3
Where t1.id = t2.id
And t2.id = t3.id
Group by t3.id, t3.name, t3.phone_no, t1.reg_dtm
Order by t2.id, t1.reg_dtm
The above query returns the following error
ORA-00979: not a GROUP BY expression
But if i change it so that everything in the group by clause is in the order by clause then it works.
Select t3.id, t3.name, t3.phone_no, t1.reg_dtm, count(t1.reg_dtm)
from tableA t1, tableB t2, tableC t3
Where t1.id = t2.id
And t2.id = t3.id
Group by t3.id, t3.name, t3.phone_no, t1.reg_dtm
Order by t3.id, t3.name, t3.phone_no, t1.reg_dtm
What exactly is the reason for this?
I think the problem is possibly because t2.id shown in the order by statement on the first query is not part of the group by statement. If this is the cause then why does it matter? I have never experienced this before and didn't think that there was any relationship between the group by and the order by statements.
I tested the above on Oracle 10G as well as MySQL.
Thanks in advance
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
ORDER BY 子句在 SELECT 语句中的其他所有内容执行之后运行；在 GROUPing 场景中，结果集仅限于用于聚合数据的列。如果您没有在初始结果集中指定列，处理引擎将无法理解如何处理请求的输出。
换句话说，由于您的查询不会为 t2.id 和 t1.id 返回不同的值（因为它们未在 GROUP BY 子句中使用），因此引擎无法按该顺序返回数据。
The ORDER BY clause runs after everything else in the SELECT statement executes; in a GROUPing scenario, the result set is limited to the columns used to aggregate the data. If you don't have a column specified in your initial resultset, the processing engine doesn't understand what to do with the requested output.
In other words, since your query doesn't return distinct values for t2.id and t1.id (since they're not used in the GROUP BY clause), the engine can't return the data in that order.
从技术上讲，
GROUP BY子句中未提及的列不应出现在结果集中（除非它们被聚合，即max(columnname)）——那么如何ORDER BY它们有意义吗？换句话说，这样的查询意味着什么？然而，MySQL（我不知道其他的）允许您选择不在 GROUP BY 中的列，这会给初学者带来很多困惑，想知道为什么他们会得到奇怪的查询结果。
作为旁注，您可能希望避免隐式联接，如此处所述。
The columns that are not mentioned in the
GROUP BYclause technically should not be in the result set (unless they're aggregated, i.e.max(columnname))-- so how does it make sense toORDER BYthem? In other words, what would such a query even mean?However, MySQL (I don't know about others) allows you to select columns that are not in the
GROUP BY, which leads to lots of confusion for beginners wondering why they get strange query results.As a sidenote, you may want to avoid implicit joins, as discussed here.
一般来说，您不能按不在 GROUP BY 子句中且不是 SELECT 列表中聚合函数的列进行排序，因为数据库无法确定性地对列进行排序。结果。数据库通常不知道这样的列如何在最终结果中聚合，因此它不知道如何处理结果中的一行是聚合基表中的行的结果1 和 的值4，结果中的另一行是聚合基表中值为 2 & 的行的结果。 3. 无论选择哪种方式对结果进行排序都可能被认为是不正确的。
现在，在这种特殊情况下，由于您在所有三个表之间对 ID 进行内部联接，理论上数据库可以足够智能地识别 ORDER BY t2.id， t1.id 在语义上等同于可以确定性地评估的
ORDER BY t3.id, t3.id。然而，我不知道有哪个数据库在其优化器中内置了这种查询转换。当这种分析出错时引入的潜在错误的权衡往往会反对将其包含在内，因为从集合论的角度来看，您尝试运行的查询没有多大逻辑意义。In general, you cannot order by columns that are not in the
GROUP BYclause and that are not aggregate functions in theSELECTlist because the database has no way to deterministically sort the results. The database doesn't generally know how such a column would be aggregated in the final results so it doesn't know how to handle the case where one row in a result is, for example, the result of aggregating rows in the base table with values of 1 & 4 and another row in the result is the result of aggregating rows in the base table with values of 2 & 3. Either way it chooses to sort the results could be considered incorrect.Now, in this particular case, since you're doing inner joins on
IDbetween all three tables, the database could, in theory, be smart enough to recognize thatORDER BY t2.id, t1.idis semantically equivalent toORDER BY t3.id, t3.idwhich can be evaluated deterministically. I don't know of any database, however, that has built this sort of query transformation into its optimizer. And the trade-off of potential bugs introduced when such an analysis goes wrong would tend to argue against including it when the queries you're trying to run don't make much logical sense from a set theory standpoint.您只能按 select 子句中的列进行排序 - 因此
order by t3.ud, t1.reg_dtm应该可以做到这一点，并且具有相同的语义。You can order only by columns that are in the select clause - so an
order by t3.ud, t1.reg_dtmshould do it and would have the same semantic.实际上，如果你仔细阅读 SQL，在第一个语句中你会按 T3.ID 进行分组，但会按 T2.ID 进行排序。
如果那是您正在运行的真实 SQL，那么您唯一的问题就是您有一个拼写错误。
Actually, if you read the SQL carefully, in the first statement you group by T3.ID, but sort by T2.ID.
If that's the real SQL that you're running, then your only problem is that you have a typo.