SQL Order By 无法正常工作 [英] SQL Order By not working properly

问题描述

我有一张这样的桌子

 CREATE TABLE [dbo].[tbl_LandRigs](
    [ID] [int] IDENTITY(700000,1) NOT NULL,
    [Company] [nvarchar](500) NULL,
    [Rig] [nvarchar](500) NULL,
    [RigType] [nvarchar](200) NULL,
    [DrawWorks] [nvarchar](500) NULL,
    [TopDrive] [nvarchar](200) NULL,
    [RotaryTable] [nvarchar](500) NULL,
    [MudPump] [nvarchar](500) NULL,
    [MaxDD] [nvarchar](50) NULL,
    [Operator] [nvarchar](500) NULL,
    [Country] [nvarchar](200) NULL,
    [Location] [nvarchar](500) NULL,
    [OPStatus] [nvarchar](200) NULL,
    [CreatedDate] [datetime] NULL,
    [CreatedByID] [int] NULL,
    [CreatedByName] [nvarchar](50) NULL,
 CONSTRAINT [PK_tbl_LandRigs] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

我正在尝试按降序从 MaxDD 列获取数据

And I am trying to get data from MaxDD column in Descending order

SELECT  distinct "MaxDD" FROM [tbl_LandRigs] ORDER BY "MaxDD" Desc

但这会按以下顺序返回数据按照我的计算，4000一定是其他人的第一个值.但是这个结果让我很惊讶.有人能帮我解决这个问题吗?

But this returns data in following order According to my calculation 4000 must be the first value followed by others.But this results astonished me.Can any one help me out in this?

推荐答案

您将它们存储为文本 (nvarchar)，这就是您获得 字典顺序.这意味着从左到右比较每个字符.因此 4000 比 30000 高"(最后一个零无关紧要，因为前 4 个已经高于 3).

You are storing them as text(nvarchar), that's why you get lexographical order. That means every character is compared with each other from left to right. Hence 4000 is "higher" than 30000 (the last zero doesn't matter since the first 4 is already higher than the 3).

所以正确的方法是将其存储为数值.但是，这似乎是不可能的，因为您还使用 16.000 和 4.1/2"DP 之类的值.然后我会添加另一列，一列用于您要排序的数值，另一列用于文字表示.

So the correct way is to store it as a numeric value. However, that seems to be impossible since you also use values like 16.000 with 4.1/2"DP. Then i would add another column, one for the numeric value you want to order by and the other for the textual representation.

这篇关于SQL Order By 无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！