如何在SQL中将字符串作为数字排序? [英] How to sort strings as numbers in SQL?

问题描述

谁能告诉我该如何解决?我的课程顺序是按课程编号递增(例如，CS 20，CS 25，CS 100都是课程编号).它是在计算第一个数字而不是整数:

Can anyone tell me how to fix this? My order by is by coursenum (for example, CS 20, CS 25, CS 100 are all course numbers) ascending. It's counting the first digit instead of the whole number though:

-----------------------------------------
Course                              Grade
-----------------------------------------
CS 120 Intro to Java Programming        A
CS 140 Structured Analysis              A
CS 20 Intro to Computers                F
CS 25 Intro to Programming              B

推荐答案

此处的问题是您的数据库正在执行字符串排序，而不是数字排序.计算机将无法查看该数据并推断出语义排序顺序.字符串排序是通过获取两个字符串，然后比较每个字符直到一个字符先于另一个字符来完成的.因此，在您的示例中，每个课程都以"CS"开头，但是第四个字符是数字字符(与数字类型不同！). "1"比"2"低，然后在"1"中，"2"比"4"低，依此类推.

The problem here is that your database is doing a string sort, rather than a numeric sort. A computer won't be able to look at that data and infer a semantic sorting order. A string sort is done by taking two strings, and comparing each character until one character comes before another. So, in your example, each of those courses begins with "CS ", but then the 4th character is a numeric character (which is not the same as a numeric type!). the "1"s sort as lower than the "2"s, and then in the "1"s, the "2" sorts lower than the "4", and so on.

要解决此问题，您可能需要将课程号("CS 020")零填充，或者需要使用带有课程号的单独数字列进行排序.

To solve this, you'd either need to zero pad the course number ("CS 020") or you'd need a separate numeric column with the course number to sort on.

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