TSQL ORDER BY 首先或最后一个空值(在底部或顶部) [英] TSQL ORDER BY with nulls first or last (at bottom or top)
问题描述
我有一个日期列,其中包含一些 NULL
.我想按日期列 ASC 排序,但我需要 NULL
位于底部.如何在 TSQL 上做到这一点?
I have a date column which has some NULL
. I want to order by the date column ASC, but I need the NULL
s to be at the bottom. How to do it on TSQL?
推荐答案
在标准 SQL 中,您可以指定放置空值的位置:
In standard SQL you can specify where to put nulls:
order by col asc nulls first
order by col asc nulls last
order by col desc nulls first
order by col desc nulls last
但是 T-SQL 不符合这里的标准.NULL 的顺序取决于你在 T-SQL 中是升序还是降序:
but T-SQL doesn't comply with the standard here. The order of NULLs depends on whether you sort ascending or descending in T-SQL:
order by col asc -- implies nulls first
order by col desc -- implies nulls last
对于整数,您可以简单地按负数排序:
With integers you could simply sort by the negatives:
order by -col asc -- sorts by +col desc, implies nulls first
order by -col desc -- sorts by +col asc, implies nulls last
但这对于日期(或与此相关的字符串)是不可能的,因此您必须首先按 is null/is not null 排序,然后再按您的列排序:
But this is not possible with dates (or strings for that matter), so you must first sort by is null / is not null and only then by your column:
order by case when col is null then 1 else 2 end, col asc|desc -- i.e. nulls first
order by case when col is null then 2 else 1 end, col asc|desc -- i.e. nulls last
这篇关于TSQL ORDER BY 首先或最后一个空值(在底部或顶部)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!