blocks|key|4548338|text|这能行得通吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4548339|SELECT+.......+ORDER+BY+COALESCE(col1,col2,col3,etc)+IS+NULL|code-block|syntax|javascript|4548340|我对你的措辞“最后一列都是空列”感到有点困惑。如果您希望所有空值都在特定列的末尾，请使用以下命令：|4548341|SELECT+.......+ORDER+BY+col1+IS+NULL|4548342|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

could this work?

<pre><code>SELECT ....... ORDER BY COALESCE(col1,col2,col3,etc) IS NULL
</code></pre>

I am kind of confused by your wording "all NULL columns last". If you want all NULL values last in a particular column, use this:

<pre><code>SELECT ....... ORDER BY col1 IS NULL
</code></pre>

blocks|key|2512668|text|你可以这样做来伪装它：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2512669|select+*+from+test
order+by+case+ordercol+when+null+then+1+else+0+end,+ordercol+|code-block|syntax|javascript|2512670|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can do something like this to fake it:

<pre><code>select * from test
order by case ordercol when null then 1 else 0 end, ordercol 
</code></pre>

blocks|key|2911405|text|虽然我有点喜欢蓝胡子的答案，但这个变体并不关心提供正确数据类型的有效“假”值。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2911406|ORDER+BY+CASE+WHEN+SOMECOL+IS+NULL+THEN+1+ELSE+0+END,+SOMECOL|code-block|syntax|javascript|2911407|或者，即使您想使用假值，我也更倾向于使用IFNULL！|offset|length|style|CODE|2911408|ORDER+BY+IFNULL(SOMECOL,-9999)|2911409|正如迈克尔所指出的，SQLite使用IFNULL。您也可以使用ANSI-SQL通用版本的COALESCE。|2911410|entityMap^0|0|0|K|6|0|0|I|6|18|8|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@$I|V|J|W|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|Y|8|@$I|Z|J|10|K|L]|$I|11|J|12|K|L]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|13|8|@]|9|@]|A|$]]]|R|$]]

While I somewhat like Blorgbeard's answer, this variant doesn't care about supplying a valid 'fake' value of the right datatype.

<pre><code>ORDER BY CASE WHEN SOMECOL IS NULL THEN 1 ELSE 0 END, SOMECOL
</code></pre>

Alternatively, even if you wanted to use a fake value, I would prefer <code>IFNULL</code>!

<pre><code>ORDER BY IFNULL(SOMECOL,-9999)
</code></pre>

As Michael noted, SQLite uses <code>IFNULL</code>. You can use the ANSI-SQL universal version <code>COALESCE</code> as well.

blocks|key|2911512|text|我遇到了同样的问题。我发现这是可行的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2911513|(我没有找到任何用于SQLite的isnull函数)|offset|length|style|CODE|2911514|order+by+ifnull(column_what_you_want_to_sort,'value+in+case+of+null')|code-block|syntax|javascript|2911515|entityMap^0|0|H|6|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|S|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I ran into the same problem.
I found out this could work:

(I didn't find any <code>isnull</code> function for SQLite)

<pre><code>order by ifnull(column_what_you_want_to_sort,'value in case of null')
</code></pre>

blocks|key|4638685|text|我的解决方案是颠倒priority列的范围并使用order+by+desc+(降序排序)。这会一次性将空值放在末尾。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4638686|显然，只有当你控制你的priority排序列时，这才能起作用，但如果你能控制它，那就太棒了！|4638687|如果这不起作用，我发现联合查询起作用了。只需对两个查询执行union操作，并在第一个查询上添加约束where+not+null，在第二个查询上添加where+null。第二个查询将位于第一个查询的末尾。|4638688|entityMap^0|9|8|O|D|0|B|8|0|T|5|1D|E|21|A|0^^$0|@$1|2|3|4|5|6|7|L|8|@$9|M|A|N|B|C]|$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|T|8|@$9|U|A|V|B|C]|$9|W|A|X|B|C]|$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|J|3|-4|5|6|7|10|8|@]|D|@]|E|$]]]|K|$]]

My solution was to reverse the range of my <code>priority</code> column and use <code>order by desc</code> (sort in descending order). This puts nulls at the end in one go. 

Clearly this only works if you control your <code>priority</code> ordering column, but it's pretty great if you can swing it!

If that's doesn't work, I found a union query worked. Simply <code>union</code> two queries and add the constraint <code>where not null</code> on the first and <code>where null</code> on the second. The second query will be at the end of the first.

blocks|key|4638781|text|在我的例子中，我想先对空列进行降序排序。在查询之后，我从while循环向列表中添加记录，所以我只是检查列是否为NULL，并使用ArrayList.add(0,+Object)来实现这一点。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4638782|entityMap^0|1R|O|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

In my case I wanted to sort descending with NULL columns first. I am adding records to a List from a while loop after querying, so I simply checked if the column was NULL and used <code>ArrayList.add(0, Object)</code> to achieve this.

blocks|key|4548724|text|SQLite+3.30.0%2B支持NULLS+FIRST/LAST子句。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4548725|4548726|+Release+History+(v3.30.0+-+2019-10-04)|blockquote|4548727|4548728|4548729|在ORDER+BY子句中添加对NULLS+FIRST和NULLS语法的支持。|ordered-list-item|4548730|4548731|4548732|出于排序目的，|4548733|+ORDER+BY+SQLite会将空值视为小于任何其他值。因此，NULL自然会出现在ASC+order-by的开头和DESC+order-by的结尾。可以使用"ASC+NULLS+LAST“或"DESC+NULLS+FIRST”语法更改这一点。|BOLD|4548734|4548735|4548736|SELECT+*+FROM+t+ORDER+BY+c+NULLS+LAST;|code-block|syntax|javascript|4548737|4548738|entityMap|0|LINK|mutability|MUTABLE|url|https://www.sqlite.org/changes.html|1|https://www.sqlite.org/lang_select.html#orderby^0|G|G|0|0|1|12|0|0|0|0|0|0|0|0|24|1B|1|8|1|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1D|8|@$9|1E|A|1F|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|1G|8|@]|D|@]|E|$]]|$1|G|3|H|5|I|7|1H|8|@]|D|@$9|1I|A|1J|1|1K]]|E|$]]|$1|J|3|-4|5|6|7|1L|8|@]|D|@]|E|$]]|$1|K|3|-4|5|6|7|1M|8|@]|D|@]|E|$]]|$1|L|3|M|5|N|7|1N|8|@]|D|@]|E|$]]|$1|O|3|-4|5|6|7|1O|8|@]|D|@]|E|$]]|$1|P|3|-4|5|6|7|1P|8|@]|D|@]|E|$]]|$1|Q|3|R|5|6|7|1Q|8|@]|D|@]|E|$]]|$1|S|3|T|5|I|7|1R|8|@$9|1S|A|1T|B|U]]|D|@$9|1U|A|1V|1|1W]]|E|$]]|$1|V|3|-4|5|6|7|1X|8|@]|D|@]|E|$]]|$1|W|3|-4|5|6|7|1Y|8|@]|D|@]|E|$]]|$1|X|3|Y|5|Z|7|1Z|8|@]|D|@]|E|$10|11]]|$1|12|3|-4|5|6|7|20|8|@]|D|@]|E|$]]|$1|13|3|-4|5|6|7|21|8|@]|D|@]|E|$]]]|14|$15|$5|16|17|18|E|$19|1A]]|1B|$5|16|17|18|E|$19|1C]]]]

SQLite 3.30.0+ is supporting <code>NULLS FIRST/LAST</code> clauses.
<blockquote>
<a href="https://www.sqlite.org/changes.html" rel="noreferrer">Release History (v3.30.0 - 2019-10-04)</a>
</blockquote>
<blockquote>
<ol start="2">
<li>Add support for the NULLS FIRST and NULLS LAST syntax in ORDER BY clauses.</li>
</ol>
</blockquote>
<hr />
<blockquote>
<a href="https://www.sqlite.org/lang_select.html#orderby" rel="noreferrer">ORDER BY</a>
</blockquote>
<blockquote>
SQLite considers NULL values to be smaller than any other values for sorting purposes. Hence, NULLs naturally appear at the beginning of an ASC order-by and at the end of a DESC order-by. This can be changed using the &quot;ASC NULLS LAST&quot; or &quot;DESC NULLS FIRST&quot; syntax.
</blockquote>
<pre><code>SELECT * FROM t ORDER BY c NULLS LAST;
</code></pre>
<a href="https://www.db-fiddle.com/f/kiTxgusxzjmAyKZidWZir9/0" rel="noreferrer">db-fiddle.com demo</a>

blocks|key|2512903|text|如果有人在3.30.0之前的SQLite版本上寻找ASC+NULLS+LAST功能(尤其是文本列)，请添加此答案(因此该功能不可用)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2512904|要将空值放在最后，请将它们替换为一个值，然后确保所有其他值都小于它：|2512905|SELECT+*+FROM+customers+ORDER+BY
CASE
++++WHEN+Company+IS+NULL+THEN+'2'
++++ELSE+'1'+%7C%7C+Company
END
ASC;|code-block|syntax|javascript|2512906|此外，对于整型列，如果需要保持数字比较(而不是使用文本切换字典序)，则只需将NULL更改为某些文本。在SQL中比较文本>整数|2512907|SELECT+*+FROM+customers+ORDER+BY
CASE
++++WHEN+IntegerColumn+IS+NULL+THEN+'null'
++++ELSE+IntegerColumn
END
ASC;|2512908|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Adding this answer in case anyone is looking for the ASC NULLS LAST functionality (especially for TEXT columns) on an SQLite version before 3.30.0 (so it is unavailable).
To place the nulls last, replace them with a value, and then make sure all other values are lower then it:
<pre><code>SELECT * FROM customers ORDER BY
CASE
 WHEN Company IS NULL THEN '2'
 ELSE '1' || Company
END
ASC;
</code></pre>
Also, for INTEGER columns, if keeping number comparison is needed (as opposed to switching lexicographic with text) then just change the NULL to some text. in SQL comparison TEXT &gt; INTEGER
<pre><code>SELECT * FROM customers ORDER BY
CASE
 WHEN IntegerColumn IS NULL THEN 'null'
 ELSE IntegerColumn
END
ASC;
</code></pre>

I'd like to sort my result with all NULL columns last (<code>NULLS LAST</code>), as specified in the <a href="http://en.wikipedia.org/wiki/Order_by" rel="noreferrer">SQL:2003 extension T611</a>. Sadly, SQLite seems to not support it. Is there a clever workaround?

How to do NULLS LAST in SQLite?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我希望按照中的指定，将所有空列最后(NULLS LAST)对结果进行排序。遗憾的是，SQLite似乎并不支持它。有没有聪明的变通办法？

问如何在SQLite中最后做空值？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQLite中最后做空值？EN