如何在SQL中按升序对字符串列进行排序 [英] How to sort string column in ascending order in SQL

问题描述

10年级

11年级

12年级

1ST等级

2ND等级

3RD等级

4TH等级

5TH等级

6TH等级

7TH等级

8TH Grade

9TH Grade

Adnavced

Mains



I在我的sql表中有上面的数据，我想按升序排序，怎么做？

10TH Grade
11TH Grade
12TH Grade
1ST Grade
2ND Grade
3RD Grade
4TH Grade
5TH Grade
6TH Grade
7TH Grade
8TH Grade
9TH Grade
Adnavced
Mains

I have above data in my sql table, I want to sort them in ascending order, how to do it ?

推荐答案

如果在sql中有'VARCHAR'数据类型那么它将以升序为1,10,11，您可以使用以下查询来解决您的问题

If you have 'VARCHAR' datatype in sql then it will take 1, 10, 11 as ascending order, you can use following query to resolve your issue

select col from yourtable order by length(col),col

有很多困难，如果这就是你要存储的东西。

字符串排序是基于字符的，并且整个比较取决于第一个不同字符 - 所以排序顺序不是数字：

With a lot of difficulty, if that's what you are storing.
String sorting is character based, and the whole comparison depends on the first different character - so the sort order will not be numeric:

1
10
11
12
...
2
20
21

在你的情况下，情况更糟，因为TH，ST，ND后缀也在比较中计算。



我要做的是设置一张单独的桌子

In your case it's even worse, because the "TH", "ST", "ND" suffixes are counted in the comparison as well.

What I would do is set up a separate table

Grade Description
(int) (NVARCHAR(20))
1     1ST Grade
2     2ND Grade
3     3RD Grade
4     4TH Grade
5     5TH Grade
6     6TH Grade
7     7TH Grade
8     8TH Grade
9     9TH Grade
10    10TH Grade
11    11TH Grade
12    12TH Grade
99    Advanced
199   Mains

并将Grade值与您的其他数据一起存储，然后按此排序，并使用JOIN检索描述。



任何其他方法都会变得笨拙且容易出错。

And store the Grade value with your other data, sort by that, and use a JOIN to retrieve the Description.

Any other method is going to be clumsy and error prone.

这篇关于如何在SQL中按升序对字符串列进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！