ORACLE 和 PostgreSQL 之间的不同默认排序 [英] Different Default ordering between ORACLE and PostgreSQL

问题描述

我有一个简单的 ORACLE 查询，我应该重写它以在 postgresql 上运行，输出与下面相同

I have a simple ORACLE Query which I should rewrite it to be run on postgresql with same output as below

 Select X,Y FROM table_name order by Y

如果表中只有以下数据

这里是PG和oracle在数据排序上的区别

Here you are the difference between PG and oracle in ordering the data

您知道为什么会出现这种差异吗?

Do you have idea why such this difference occurs?

推荐答案

不同的默认排序

没有默认排序"这样的东西；- 无论是在 Oracle 中还是在 Postgres(或任何其他关系数据库中).关系数据库中的表表示无序集合.

There is no such thing as "default ordering" - neither in Oracle nor in Postgres (or in any other relational database). Tables in a relational database represent un-ordered sets.

您正在对两行(所有)包含相同值的列进行排序.这与根本不排序基本相同，因为您尚未定义任何排序标准来打破这些联系.如果没有额外的排序列，数据库可以自由地以它喜欢的任何顺序返回具有相同排序值的行.

You are sorting on a column that contains the same value for both (all) rows. This is essentially the same as not sorting at all, because you have not defined any sort criteria to break those ties. Without an additional sort column the database is free to return the rows with the same sort value in any order it likes.

如果您希望按列 x 对行进行排序，则需要将该列包含在 order by

If you want the rows sorted by column x you need to include that column in the order by

select X,Y 
FROM table_name 
order by x,y;

或者您可能想要 order by y,x - 从您的问题(以及难以阅读的屏幕截图)中不清楚

or maybe you want order by y,x - it's not clear from your question (and the hardly readable screen shots)

这篇关于ORACLE 和 PostgreSQL 之间的不同默认排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！