如何在SQL中按升序对字符串列进行排序

本文介绍了如何在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.