如何在SQL 2008中对VARCHAR进行排序 [英] HOW TO ORDER THE VARCHAR in SQL 2008
问题描述
如何订购varchar fiels如下输出
例如我有账户没有价值如
FD2Y-01-10
NS-01-1
DDL-01-3
NS-01-10
NS-01-5
FD2Y-01-1
FD2Y-01-5
NS-01-2 >
DDL-01-13
DDL-01-1
从账户订购账户中选择账户
和订单价值应该是
DDL-01-1
DDL-01-3
DDL-01-13
FD2Y-01-1
FD2Y-01-5
FD2Y-01-10 >
NS-01-1
NS-01-2
NS-01-5
NS-01-10
How to order the varchar fiels as following output
eg I Have ACCOUNT NO values like
FD2Y-01-10
NS-01-1
DDL-01-3
NS-01-10
NS-01-5
FD2Y-01-1
FD2Y-01-5
NS-01-2
DDL-01-13
DDL-01-1
SELECT ACCOUNTNO FROM MAMACCOUNTOPENING ORDER BY ACCOUNTNO
and the order values should be like
DDL-01-1
DDL-01-3
DDL-01-13
FD2Y-01-1
FD2Y-01-5
FD2Y-01-10
NS-01-1
NS-01-2
NS-01-5
NS-01-10
推荐答案
我测试了这个,这个工作正常。这里test是我的表,其中一列名为name,其中包含您提到的值。
I tested this and this is working fine. Here test is my table having one column named name with values you mentioned.
select name from test
order by substring(name, 1,(len(name)-charindex('-', name))-1), name
希望这适合你。 :)
Hope this works well with you. :)
我认为你可以在 - 的基础上拆分列并将其转换为三列并按顺序放在后面
<分拆列的
I think you can split the column on the basis of "-" and convert it in to three columns and put it after order by clause
for spliting columns
select LEFT(name, CHARINDEX(' ', name + '-') -1),
STUFF(name, 1, Len(Name) +1- CHARINDEX('-',Reverse(name)), '')
from somenames
以上查询不是你的输出,但这是你的例子......
above query not for your output but this is the example for you .....
在你的问题中 VARCHAR 值以...开头一个字母,所以一个普通的ORDER BY Column_name子句可以用来获得所需的结果。
如果你有任何问题同样采取屏幕截图或用数据库结构的描述写完整的查询。
另外:转到LINK了解更多关于 VARCHAR 。
http://msdn.microsoft.com/en-us /library/ms176089.aspx [ ^ ]
HAPPY CODING ....
我的博客地址: http://basetutorial.blogspot.in/ [ ^ ]
As in your "Question" the VARCHAR values starts with an alphabet, So a normal "ORDER BY Column_name" clause can be used to get the required result.
If you have any problem with the same take a screen shot or write the full Query with the description of database structure.
ALSO: Go to the LINK to Know more about VARCHAR.
http://msdn.microsoft.com/en-us/library/ms176089.aspx[^]
HAPPY CODING....
MY BLOG ADDRESS: http://basetutorial.blogspot.in/[^]
这篇关于如何在SQL 2008中对VARCHAR进行排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!