空排序最后 [英] Sorting Nulls last

问题描述

想最后对Nulls和Blanks进行排序，并已阅读按顺序排序的所有使用Coalesce函数或If的解决方案.

Want to sort Nulls and Blanks last and have read all the solutions that use either the Coalesce function or the If in the order by column.

我的问题对我来说不是所有的工作，因为要排序的列是由创建查询的PHP脚本动态指定的，而我的某些列在我的联接的两个表中.

MY problems is non of these work for me because the column I am sorting on is specified dynamically by the PHP script that creates the query and some of my columns are in two tables in my join.

   $sortcol="boat";
   $sql= "SELECT fleet,b.boat as boat,owner FROM boats as b 
   LEFT JOIN owners as o ON  b.boat=o.boat 
   ORDER BY $sortcol";

这很好用，我可以更改$ sortcol变量，我的输出列表也很好用，除了null和空格在顶部.

This works great, I can change the variable $sortcol and my output listing works great except the nulls and blanks are at the top.

根据我尝试过的其他帖子

Based on other postings I tried this

   $sortcol="boat";
   $sql= "SELECT fleet,b.boat as boat,owner FROM boats as b 
   LEFT JOIN owners as o ON  b.boat=o.boat 
   ORDER BY IF($sortcol is NULL,1,0), $sortcol";

这将引发错误"ORDER BY子句中的列船模棱两可".显然，它希望按顺序排列b.boat，但是出于某种原因，我不会对此进行讨论. 看来，每当我尝试在orderby子句中使用函数时，都无法使用列别名.

This throws the error "column boat in ORDER BY clause is ambiguous". Obviously it wants b.boat in the order by, but for reasons I won't get into that is problematic. It appears that anytime I try to use a function in the orderby clause I can't use the column alias.

有什么好办法可以解决这个问题吗?

Any ideas for an elegant solution to this??

推荐答案

您是对的.出于我无法理解的原因，MySQL 接受一个模糊的ORDER BY，只要您提供的名称没有任何方式处理(我想不到的任何方式)也许其他人存在.

You're right. For no reason that I can fathom, MySQL accepts an ambiguous ORDER BY as long as the name you supply is not handled in any way (no way that I could think of. Maybe others exist).

就这样，模棱两可就被拒绝了.

As soon as it is, ambiguousness gets rejected.

这被接受(并且是多余的):

This is accepted (and redundant):

select b.id, a.name as name
    FROM client AS a JOIN client AS b ON (a.id = b.id)
    ORDER BY name, name;

而COALESCE(name, '')，name IS NULL，name OR NULL均被拒绝.

显而易见的解决方案是为别名使用不同的名称，该名称不会出现在任何一个表中.

The obvious solution is to use a different name for the alias, one that does not appear in either table.

另一种可能性是创建一个嵌套查询:

Another possibility would be to create a nested query:

SELECT * FROM ( your query here, without ORDER ) AS original
ORDER BY IF($sortcol is NULL,1,0), $sortcol;

也就是说:

$sortcol="boat";
$sql = <<<SQL
   SELECT * FROM (
      SELECT fleet,b.boat as boat,owner FROM boats as b 
         LEFT JOIN owners as o ON  b.boat=o.boat 
   ) AS original
   ORDER BY IF($sortcol is NULL,1,0), $sortcol;
SQL;

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