这是字符串得按字符串的排序规则,想按数字来就得把它拆分转化成数字再排序
WITH A AS (
SELECT '2203002-AA(1)' [S]
UNION ALL
SELECT '2203002-AA(2)' [S]
UNION ALL
SELECT '2203002-AA(3)' [S]
UNION ALL
SELECT '2203002-AA(4)' [S]
UNION ALL
SELECT '2203002-AA(5)' [S]
UNION ALL
SELECT '2203002-AA(6)' [S]
UNION ALL
SELECT '2203002-AA(7)' [S]
UNION ALL
SELECT '2203002-AA(8)' [S]
UNION ALL
SELECT '2203002-AA(9)' [S]
UNION ALL
SELECT '2203002-AA(10)' [S]
UNION ALL
SELECT '2203002-AA(100)' [S]
)
SELECT *,(CASE WHEN PATINDEX('%([0-9])%',S)>0 THEN SUBSTRING(S,1,PATINDEX('%([0-9])%',S)-1) WHEN PATINDEX('%([0-9][0-9])%',S)>0 THEN SUBSTRING(S,1,PATINDEX('%([0-9][0-9])%',S)-1) WHEN PATINDEX('%([0-9][0-9][0-9])%',S)>0 THEN SUBSTRING(S,1,PATINDEX('%([0-9][0-9][0-9])%',S)-1) ELSE '' END ) [前缀],(CASE WHEN PATINDEX('%([0-9])%',S)>0 THEN SUBSTRING(S,PATINDEX('%([0-9])%',S)+1,1) WHEN PATINDEX('%([0-9][0-9])%',S)>0 THEN SUBSTRING(S,PATINDEX('%([0-9][0-9])%',S)+1,2) WHEN PATINDEX('%([0-9][0-9][0-9])%',S)>0 THEN SUBSTRING(S,PATINDEX('%([0-9][0-9][0-9])%',S)+1,3) ELSE -1 END ) [后续] FROM A ORDER BY 2 DESC,3 DESC