如何在sql server中自定义排序数据

Question

我使用的是Sql server 2008 r2，我需要按以下数据排序：

CardNo

R-1  
R-2
R-12
R-1A
R-3
R-2B

结果应该如下所示

CardNo

R-1  
R-1A
R-2
R-2B
R-3
R-12

我在order by子句中尝试了不同的组合，但都没有用，比如：

select * from [Coll2012-13] where  
    SUBSTRING(CardNo, 1, 1) IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'W', 'X', 'Y', 'Z') 
    AND SUBSTRING(CardNo, 2, 1) IN ('-') 
    AND SUBSTRING(CardNo, 3, 1) IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0')
    and Landmark='Anandbagh' order by LEN(CardNo),CardNo ASC

Answer 1

假设:值的格式始终为‘Letter-字母数字字符串’

试试这个：

select card_no
from [Coll2012-13]
order by left(card_no,1),
case
when isnumeric(substring(card_no,3, len(card_no))) = 1
then cast(substring(card_no,3, len(card_no)) as int)
else cast(substring(card_no,3, patindex('%[A-Z]%',substring(card_no,3, len(card_no)))-1) as int)
end,
case 
when patindex('%[A-Z]%', substring(card_no,3,len(card_no))) > 0
then substring(card_no,patindex('%[A-Z]%', substring(card_no,3,len(card_no)))+2,1)
end

工作原理:首先检查起始字母。接下来，检查字母数字部分是否实际上只是数字。如果是，则获取该部分的整数值。如果不是，则获取它的数值部分并将其用作排序值。最后，如果字母数字部分包含字母，则将其用作另一个排序值。

演示here。

Answer 2

尝尝这个

with cte
AS
(
    select 
       * 
       ,substring(CardNo, 1, charindex('-')-1) RealRank1
       ,substring(CardNo, charindex('-')+ 1, 10) RealRank2
    from 
       [Coll2012-13] 
)
select 
    *
from 
    cte
order by
    RealRank1
    ,RealRank2

Answer 3

一个简单但“应该维护”的解决方案是创建一个排序表，并将该表连接到您的结果集。该表包含所有CardNo值，并将排序顺序与它们相关联。

编辑：

CREATE TABLE CardNoOrderHelper (
    CardNo VARCHAR(16)
    , OrderRank INT CONSTRAINT DF_CardNoOrderHelper_OrderRank DEFAULT 0
    , PRIMARY KEY CLUSTERED (
        CardNo ASC
    )
);

-- Fill your table with the expected sort order (dinstinct insert, than adjust the order ranks)

SELECT
    *
FROM
    [Coll2012-13] AS T
    LEFT JOIN CardNoOrderHelper CH
        ON T.CardNo = CH.CardNo
ORDER BY
    T.OrderRank

正如我所说的，你必须维护这个表。当您的结果集很小时，可以手动完成此操作。