查询要求"Order By"子句中的列与"Group by"子句中的列匹配 [英] Query insisting on columns in the 'Order By' clause match columns in the 'Group by' clause

问题描述

这个有点奇怪.我正在尝试运行以下查询联接3个表.

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

但是如果我更改它，以便group by子句中的所有内容都在order by子句中，那么它将起作用.

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

这到底是什么原因?

我认为问题可能是因为在第一个查询的order by语句中显示的t2.id不是group by语句的一部分.如果这是原因，那为什么重要呢?我以前从未经历过这种情况，并且认为group by和order by语句之间没有任何关系.

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.

我在Oracle 10G和MySQL上测试了上述内容.

I tested the above on Oracle 10G as well as MySQL.

预先感谢

推荐答案

在SELECT语句中的所有其他命令执行之后，ORDER BY子句便运行；在GROUPING方案中，结果集限于用于聚合数据的列.如果您在初始结果集中没有指定列，则处理引擎将不了解如何处理请求的输出.

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.

换句话说，由于查询不会为t2.id和t1.id返回不同的值(因为它们未在GROUP BY子句中使用)，因此引擎无法按该顺序返回数据.

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.

这篇关于查询要求"Order By"子句中的列与"Group by"子句中的列匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！