问题描述
我的 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 列有时为空的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!