access添加排序列_在Access数据库中对数据进行排序，其中该列包含数字和字母

Please help me because I have been unable to get this right.

What is the access SQL to select this column(columnA) so that it returns a resultset with distinct values sorted first according to numbers and then to letters.

Here is the columns values: {10A,9C,12D,11G,9B,10C,9R,8T}

I have tried 'Select distinct ColumnA from tblClass order by 1'

but it returns {10A,10C,11G,12D,8T,9B,9C,9R} which is not what I want.

Thank you in advance.

解决方案

You can use the Val() function for this. From the help topic: "The Val function stops reading the string at the first character it can't recognize as part of a number"

Val(10A) will give you 10, Val(9C) will give you 9, and so on. So in your query, order by Val(ColumnA) first, then ColumnA.

SELECT DISTINCT Val([ColumnA]) AS number_part, ColumnA

FROM tblClass

ORDER BY Val([ColumnA]), ColumnA;