这是 SQLite 使空值最后出现的正确语法吗? [英] Is this the correct syntax for SQLite for making nulls appear last?

问题描述

select * from table1
order by case Language when null then 1 else 0 end, Language

无论我使用哪种方式，它总是首先显示空值.是否有允许非空值优先排序的标准方法?

No matter which way I play around with it, it always displays null values first. Is there a standard way to allow non null values to take ordering precedence?

谢谢各位

推荐答案

你不需要WHEN:

SELECT * FROM table1
ORDER BY Language IS NULL, Language

Operator IS 将返回 1，否则返回 0 (Operators).

Operator IS will return 1 on true, 0 otherwise (Operators).

处理空的TEXT，还有:

SELECT * FROM table1
ORDER BY Language IS NULL OR Language='', Language

ORDER BY 子句使用两个字段:

1. Language IS NULL OR Language=''.这是一个布尔表达式(导致 SQLite 中的 0 (false) 或 1 (true))，与 (Language IS NULL) OR (Language='')

1. Language IS NULL OR Language=''. That's a boolean expression (resulting in 0 (false) or 1 (true) in SQLite), same as (Language IS NULL) OR (Language='')

当第一个字段有相同的结果时，使用第二个字段:Language.

When first field has same results, second fiels is used: Language.

这样，每当 Language 为 NULL 或空 TEXT 时，第一个字段将是 1，依赖于那些结果在其他结果之后，计算为 0.然后，第二个字段用于对 Language 有内容的所有结果进行排序.

This way, whenever Language is NULL or empty TEXT, first field will be 1, relying those results after other results, evaluated to 0. Then, second field is used to sort all results which Language has content.

这篇关于这是 SQLite 使空值最后出现的正确语法吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！