如何在SQL Server中从orderby中排除null?

想象一下我有这张桌子:

CREATE TABLE [dbo].[Numbers] (
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Value] [int] NULL
)

我可以用一些随机数据填充它:

SET IDENTITY_INSERT [dbo].[Numbers] ON 
GO
INSERT [dbo].[Numbers] ([Id], [Value]) VALUES (1, 5)
GO
INSERT [dbo].[Numbers] ([Id], [Value]) VALUES (2, 19)
GO
INSERT [dbo].[Numbers] ([Id], [Value]) VALUES (3, 8)
GO
INSERT [dbo].[Numbers] ([Id], [Value]) VALUES (4, 94)
GO
INSERT [dbo].[Numbers] ([Id], [Value]) VALUES (5, NULL)
GO
INSERT [dbo].[Numbers] ([Id], [Value]) VALUES (6, NULL)
GO
INSERT [dbo].[Numbers] ([Id], [Value]) VALUES (7, 117)
GO
INSERT [dbo].[Numbers] ([Id], [Value]) VALUES (8, 43)
GO
SET IDENTITY_INSERT [dbo].[Numbers] OFF
GO

Now when I want to sort records by Value, the problem is that Null values are also considered into account.

select *
from Numbers 
order by [Value] asc

enter image description here

Is it possible to somehow exclude Null values from sort?

评论
  • 陀晗雨
    陀晗雨 回复
    select *
    from Numbers 
    where Value is not null
    order by [Value] asc
    

    或者,如果需要所有项目,可以将空值转换为0

    select 
      id,
      isnull(value, 0)
    from Numbers 
    order by isnull(value, 0) asc