记录的顺序不正确 [英] Records are not in proper order

问题描述

我创建了一个带有主要字段admissionNumber的学生表.....我插入了40条记录。我还在学生表中添加了一个索引

  GO  
  CREATE   NONCLUSTERED   INDEX  [AdmissionNumber] 
  ON  [dbo]。[StudentTable]（[adminno]  ASC ）

我在visual studio 2012的vbproject中通过数据源插入表....

并将dataadapter拖到表单中.. ..



现在我可以导航记录.....

问题是我的记录没有按顺序排列意味着
，第二记录是10而不是2 ....

请帮助...按正确顺序排序意味着1,2,3,4,5， 6,7,8,9,10,11

但现在我的记录是1,10,11,2,20



please help

解决方案

如果没有明确说明排序顺序，SQL可以自由重新编写按照自己喜欢的任何顺序翻转记录。

只有当你明确说出ORDER BY时才能控制退回的记录订单。

但是......你的退货订单意味着订单指定了序列，并对VARCHAR字段进行排序。当您按VARCHAR排序时，它是基于字符串的比较，它逐个字符地比较，直到找到差异。这种差异是控制秩序的原因。所以作为字符串的数字顺序是：

 1 
 10 
 11 
 12 
 13 
 ... 
 19 
 2 
 20 
 21 
 ... 

如果排序依据，您只能获得正确的数字订单数据数据。

 SELECT * FROM studenttable ORDER by adminno ASC 

我找到了一个解决方案

  SELECT  ColumnName  FROM  TableName 
  ORDER   BY   CONVERT （ INT ，替换（ColumnName，'  ColumnName'，' '））

I have created one Student Table with primary field admissionNumber..... I have insert 40 records. I have also add one index in studenttable

GO
CREATE NONCLUSTERED INDEX [AdmissionNumber]
ON [dbo].[StudentTable] ([adminno] ASC)

I have insert table through datasource in vbproject in visual studio 2012....
and drag dataadapter to a form....

Now I can navigate records.....
problem is that my records are not coming in proper order means
after 1 record, second record is 10 not 2....
please help... to sort in a proper order means 1,2,3,4,5,6,7,8,9,10,11
but now my records are 1,10,11,2,20

please help

解决方案

If you do not explicitly state a sort order, SQL is free to return records in any order it likes.
Only when you explicitly say ORDER BY do you control the returned record order.
But...your return order implies that the order sequence is specified, and is ordering a VARCHAR field. When you sort by VARCHAR, it is a string based comparison, which compares character by character until it finds a difference. That difference is what controls order. So the order of numbers as strings is:

1
10
11
12
13
...
19
2
20
21
...

You will only get a "proper" numeric order if sort by numeric data.

SELECT * FROM studenttable ORDER by adminno ASC

I found one solution

SELECT ColumnName FROM TableName
ORDER BY CONVERT(INT, Replace(ColumnName, 'ColumnName',''))

这篇关于记录的顺序不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！