1-6
1-7
1-8
1-9
先按三个字节排序
1-4-1
1-4-2
1-4-3
1-4-4
再按五个字节排序要求得到结果的算法如下。数组法或SQL法。谢谢。
1-7
1-8
1-9
1-4-1
1-4-2
1-4-3
1-7
1-8
1-9
先按三个字节排序
1-4-1
1-4-2
1-4-3
1-4-4
再按五个字节排序要求得到结果的算法如下。数组法或SQL法。谢谢。
1-7
1-8
1-9
1-4-1
1-4-2
1-4-3
---------
1-0
1-20
1-1
1-10
1-4
1-11
1-8
1-4-1
1-4-2
1-4-11
1-4-100
1-100
select * from tb order by len(nm)-len(replace(nm,"-","")),val(replace(nm,"-",""))
结果:
fld
--------
1-0
1-1
1-4
1-8
1-10
1-11
1-20
1-100
1-4-1
1-4-2
1-4-11
1-4-100
如在excel中是这样解决的
借助辅助列实现排序.
1.在B列输入公式: =SUBSTITUTE(A1,"-","0")*1
2.按B列升序排序,便可得到所需结果.
要排序的列 辅助排序列
1-5-22 105022
1-5-23 105023
1-6-10 106010
1-6-11 106011
1-6-18 106018
1-6-19 106019
1-6-3-2 1060302
1-6-3-3 1060303
1-5-11-1 10501101
1-5-11-2 10501102
1-5-11-3 10501103但用上述方法,由于辅助列
1-5-23 --- 105023
1-6-19 --- 106019
1-5-11-1 --- 10501101
与目标需求还有点差距。见下表,原因是SUBSTITUTE(A1,"-","0")*1
106019小于10501101
要求排成下列格式,如何实现。
要排序的列 辅助排序列
1-5-22 105022
1-5-23 105023
1-5-11-1 10501101
1-5-11-2 10501102
1-5-11-3 10501103
1-6-10 106010
1-6-11 106011
1-6-18 106018
1-6-19 106019
1-6-3-2 1060302
1-6-3-3 1060303
select * from tb order by len(nm)-len(replace(nm,"-","")),val(replace(nm,"-",""))
数组法:基本思路,先变成数字,排序后再转回字符串。Dim strArr() As String, blnArr(11 To 999) As Boolean
Dim i As Integer, j As Integer
Dim strTmp As StringReDim strArr(7)strArr(0) = "1-4-3"
strArr(1) = "1-9"
strArr(2) = "1-4-2"
strArr(3) = "1-6"
strArr(4) = "1-8"
strArr(5) = "1-4-1"
strArr(6) = "1-7"
strArr(7) = "1-4-4"'如果只做一次,或者 bytArr(999) 在过程内声明,第一个循环可以省掉。
For i = 11 To 999
blnArr(i) = False
Next i'这里排序了
For i = 0 To UBound(strArr)
blnArr(Val(Replace(strArr(i), "-", ""))) = True
Next ij = 0
For i = 11 To 999
If blnArr(i) Then
strTmp = CStr(i)
strArr(j) = Left(strTmp, 1) & "-" & Mid(strTmp, 2, 1) & IIf(i > 99, "-" & Mid(strTmp, 3, 1), "")
Debug.Print strArr(j)
j = j + 1
If j > UBound(strArr) Then Exit For
End If
Next i
select code, len(code)
from table1
order len(code), code
select col from table order by len(col) asc,col asc
先按col字段的长度升序,再按col升序
Dim Arr() As Variant
Arr = Array("1-6", "1-7", "1-8", "1-9", "1-4-1", "1-4-2", "1-4-3", "1-4-4")
Arr = Mppx(Arr)
For i = 0 To UBound(Arr)
MsgBox Arr(i)
Next
End SubPrivate Function Mppx(Arr() As Variant) '升序排列
Dim i, j As Long
For i = 0 To UBound(Arr)
Arr(i) = Val(Replace(Arr(i), "-", ""))
Next
For i = 0 To UBound(Arr)
For j = i + 1 To UBound(Arr)
A = Arr(i)
B = Arr(j)
If Arr(i) > Arr(j) Then
Arr(i) = B
Arr(j) = A
End If
Next j
Next iFor i = 0 To UBound(Arr)
X = Mid(Arr(i), 1, 1)
For j = 2 To Len(Arr(i))
X = X & "-" & Mid(Arr(i), j, 1)
Next
Arr(i) = X
Next
Mppx = Arr
End Function
再测试