Postgres排序问题 [英] Postgres sorting problem

问题描述

我想对DESC评分。它适用于MySQL，但适用于PostgreSQL。我得到了不同的结果。

I want to sort by rating DESC. It works with MySQL but on PostgreSQL. I get a different result.

您可以在此处看到问题： http://www.vinderhimlen.dk/konkurrencer

You can see the problem here: http://www.vinderhimlen.dk/konkurrencer

我的控制器：

  def sort_column
    Konkurrancer.column_names.include?(params[:sort]) ? params[:sort] : "rating"
  end

  def sort_direction
    %w[desc asc].include?(params[:direction]) ? params[:direction] : "desc"
  end

推荐答案

由于您的问题中缺少详细信息，因此不确定您的问题究竟是什么，或者它是不起作用的。但是至少有两个因素会影响排序，使您在MySQL和PostgreSQL中获得不同的结果。

Not sure what your issue is exactly or how it "doesn't work", from lack of details in your question. But at least two factors can affect sorting in such a way that you'd get different results in MySQL and PostgreSQL.

第一个是排序规则。 尤其是（如果您使用的是9.1 Beta）。最后，我安装了MySQL（这是前一阵子的，所以他们可能已经修复了此问题），默认情况下，它整理的东西是latin-1 / swedish，而PostgreSQL是utf-8 / english。

The first is collation. In particular if you're playing with 9.1 beta. Last I installed MySQL (which was a while ago, so they might have fixed this since), it was collating things as latin-1/swedish by default, vs utf-8/english for PostgreSQL.

另一个为空。如果有内存，MySQL总是将这些放在最后。相比之下，PostgreSQL始终将它们放置在btree索引的末尾，因此在订购asc时将它们放置在最后，在订购desc时将它们放置在第一。您可以通过使用先为空/最后为空进行排序来更改此行为。 。

The other is nulls. MySQL always places these last if memory serves. By contrast, PostgreSQL consistently places them at the end of btree indexes, and thus places them last when ordering asc and first when ordering desc. You can change this behavior by ordering using nulls first/nulls last.

在您的特定情况下，我的猜测是，您想通过对desc的最后评级为空来对进行排序到默认行为，该行为将首先放置null。

In your particular case, my guess is that you want to order by rating desc nulls last, as opposed to the default behavior which will place nulls first.

这篇关于Postgres排序问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！