SQL - 使用字母数字字符排序 [英] SQL - Order by with alphanumeric characters

问题描述

我有下表:

create table #tbl
(
 product_id nvarchar(50)
)

insert into #tbl values ('011014-A11')
insert into #tbl values ('011014-A10')
insert into #tbl values ('011014')
insert into #tbl values ('011014-A1')
insert into #tbl values ('011014-A2')

我想这样订购产品 ID(从最小到最大):

I want to order the Product IDs like this (from the smallest to the greatest):

011014
011014-A1
011014-A2
011014-A10
011014-A11

这是我尝试过的，但不起作用:

This is what I tried, but it is not working:

select product_id
from #tbl
order by product_id desc

我该怎么做?

我如何获得最大的 product_id?我试过了，但没有用:

How do I do this?

And how do I get just the greatest product_id? I tried this, but it didn't work:

select top 1 product_id
from #tbl
order by product_id desc

推荐答案

请尝试以下查询以按您预期的顺序获得结果.

Try following query to get the results in your expected order.

小提琴演示

Select product_id
From tbl              --Please change table name to #tbl in your actual query
Order by len(product_id), product_id

这篇关于SQL - 使用字母数字字符排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！