本文介绍了ORDER BY 列有时为空的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我的 SQL 看起来像这样:

My SQL looks something like this:

SELECT CompanyName , LastName , FirstName FROM ... JOIN ...
ORDER BY CompanyName , LastName , FirstName

现在的问题是 A 列有时是空的(NULL""),我不希望所有这些结果都出现在结束.

Now the problem is that column A is sometimes empty (either as NULL or ""), and I don't want all those results to turn up in the end.

在示例中,我希望将第四个条目(以 C 开头)作为第三个条目.但如果我只是 ORDER BY,就会发生这种情况:

In the example, I'd like to have the fourth entry (which starts with a C) to be the third. But if I just ORDER BY, this happens:

Avagax Bauer Frank
Bele AG Smith John
Mork AG Baggins Frodo
Chen Jun

此外,在某些情况下,我有时会有更多的 order-by 列,或多或少重要.这可能是相关的.

In addition, I sometimes have more order-by columns in a few cases, either more or less important. This might be relevant.

附录:姓氏或公司必须有一个有用的字符串.名字是完全可选的.系统是PostgreSQL(8.4,可能会迁移到9),还有SQLite.独立于供应商将是一个优势,因为已经有潜在客户在运行 Oracle 和 SQLServer.

Addendums: Either last name or company must have a useful string. First name is completely optional. The system is PostgreSQL (8.4, might migrate to 9), and also SQLite. Vendor-independence would be a plus, because there are potential customers already running Oracle and SQLServer.

推荐答案

您可能需要调整它以满足您的需求,但按照我的理解,这应该可以解决问题:

You might have to tweak this to fit your needs, but the way I understand it, this should do the trick:

SELECT CompanyName , LastName , FirstName FROM ... JOIN ...
ORDER BY COALESCE(CompanyName , LastName, FirstName),
         COALESCE(LastName, FirstName),
         FirstName

这将主要按三列中不为空的列中的哪一列先排序,然后是姓氏或名字,最后是名字.在我看来,这种排序没有多大意义,但 YMMV.

This will mainly order by whichever of the three columns that are not null first, then either by last- or first name, and lastly by first name. In my opinion, this ordering won't make much sense, but YMMV.

这篇关于ORDER BY 列有时为空的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

05-29 14:05
查看更多