在Access数据库中对数据进行排序,其中该列包含数字和字母 [英] Sorting data in Access database where the column has numbers and letters
问题描述
请帮助我,因为我一直无法获得正确的答案.
Please help me because I have been unable to get this right.
选择此列(columnA)以便返回具有不同值的结果集的访问SQL是什么,该结果集首先根据数字排序,然后按字母排序.
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.
以下是列值:{10A,9C,12D,11G,9B,10C,9R,8T}
Here is the columns values: {10A,9C,12D,11G,9B,10C,9R,8T}
我尝试过从tblClass的顺序中选择1来选择不同的ColumnA" 但它返回的不是我想要的{10A,10C,11G,12D,8T,9B,9C,9R}.
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.
谢谢.
推荐答案
您可以为此使用Val()函数.来自帮助主题:" Val函数停止读取无法识别为数字一部分的第一个字符的字符串"
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)将给您10,Val(9C)将给您9,依此类推.因此,在您的查询中,先按Val(ColumnA)排序,然后按ColumnA排序.
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;
这篇关于在Access数据库中对数据进行排序,其中该列包含数字和字母的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!