Below is the Sql query which works on oracle but not working on PostgreSQL.
下面是Sql查询，它可以在oracle上工作，但不能在PostgreSQL上工作。
select count(*) from users where id>1 order by username;
I know that order by has no meaning in this query but still why it's working on oracle. Below is error on PostgreSQL
我知道order by在这个查询中没有任何意义，但仍然是它在oracle上工作的原因。下面是PostgreSQL上的错误
ERROR: column "users.username" must appear in the GROUP BY clause or be used in an aggregate function
Position: 48
SQLState: 42803
SQLState:42803
PostgreSQL version 9.6.3
PostgreSQL 9.6.3版本
2 个解决方案
#1
3
As seen by Oracle's execution plan, there is no sorting after the rows are aggregated, which suggests that the SQL engine Oracle has implemented ignores that phrase.
正如Oracle的执行计划所看到的，在这些行被聚合之后，就没有排序了，这表明SQL引擎Oracle已经实现了忽略这个词。
Why doesn't it work in PostgreSQL -- because the people running Postgres know what they're doing ;) Just kidding, but that question would be highly speculative for me, without seeing the Oracle vs MySQL source. The bigger questions is if Oracle and MySQL allow for this by coincidence, or because Oracle owns both.
为什么它不能在PostgreSQL中工作——因为运行Postgres的人知道他们在做什么;开个玩笑，但是这个问题对我来说是很有争议的，因为我没有看到Oracle和MySQL的源代码。更大的问题是Oracle和MySQL是否出于巧合而允许这样做，或者因为Oracle同时拥有这两家公司。
Final note: If you're going to ask why similar software applications behave differently, I think it's also important to include what version you're referring to. Even different versions of the same application may follow different instructions.
最后注意:如果您要问为什么类似的软件应用程序表现不同，我认为包含您所指的版本也很重要。即使同一应用程序的不同版本也可能遵循不同的指令。
#2
2
If you are looking for the count of all records only, then there is no need of order by clause because it has no meaning even in oracle also. In such case remove order by.
如果只查找所有记录的计数，则不需要order by子句，因为即使在oracle中也没有意义。在这种情况下，删除订单。
select count(*) from users where id>1
If you are looking for username wise count, then there is a meaning of sorting on username and in such case you can use following query.
如果您正在寻找用户名的wise count，那么对于用户名的排序是有意义的，在这种情况下您可以使用以下查询。
select count(*) from users where id>1 group by username order by username;
Hope your doubt will be cleared.
希望你的怀疑能被消除。
#1
3
As seen by Oracle's execution plan, there is no sorting after the rows are aggregated, which suggests that the SQL engine Oracle has implemented ignores that phrase.
正如Oracle的执行计划所看到的，在这些行被聚合之后，就没有排序了，这表明SQL引擎Oracle已经实现了忽略这个词。
Why doesn't it work in PostgreSQL -- because the people running Postgres know what they're doing ;) Just kidding, but that question would be highly speculative for me, without seeing the Oracle vs MySQL source. The bigger questions is if Oracle and MySQL allow for this by coincidence, or because Oracle owns both.
为什么它不能在PostgreSQL中工作——因为运行Postgres的人知道他们在做什么;开个玩笑，但是这个问题对我来说是很有争议的，因为我没有看到Oracle和MySQL的源代码。更大的问题是Oracle和MySQL是否出于巧合而允许这样做，或者因为Oracle同时拥有这两家公司。
Final note: If you're going to ask why similar software applications behave differently, I think it's also important to include what version you're referring to. Even different versions of the same application may follow different instructions.
最后注意:如果您要问为什么类似的软件应用程序表现不同，我认为包含您所指的版本也很重要。即使同一应用程序的不同版本也可能遵循不同的指令。
#2
2
If you are looking for the count of all records only, then there is no need of order by clause because it has no meaning even in oracle also. In such case remove order by.
如果只查找所有记录的计数，则不需要order by子句，因为即使在oracle中也没有意义。在这种情况下，删除订单。
select count(*) from users where id>1
If you are looking for username wise count, then there is a meaning of sorting on username and in such case you can use following query.
如果您正在寻找用户名的wise count，那么对于用户名的排序是有意义的，在这种情况下您可以使用以下查询。
select count(*) from users where id>1 group by username order by username;
Hope your doubt will be cleared.
希望你的怀疑能被消除。