主页 / dba / 问题 / 147695
Accepted
M.I.M.F
Asked: 2016-08-24 23:52:30 +0800 CST

用破折号对字母数字值进行排序

  • 772

我定义了一个名为 Inventory 的表，其中包含一个varchar(45)名为ItemNameCode. 这是表格，包含以下字母数字值。

在此处输入图像描述

我们要按字母数字排序为：

MES40
PT14
PT14-403
PT14-646-649
PT14-646-649
PT15-3-11
PT15-12-42
PT15-12-572-575
PT16-164
PT16-227
PT16-62-71
PT16-136
PT16-137

我尝试了以下查询，但没有得到确切的顺序：

SELECT ItemNameCode
FROM Inventory
ORDER BY LEFT(ItemNameCode,PATINDEX('%[0-9]%',ItemNameCode)-1)
sql-server sql-server-2008-r2

1 个回答

  1. Best Answer

    在这种情况下，简单地排序ItemNameCode不会给你想要的排序。使用ORDER BY ItemNameCode会像这样对 PT15 值进行排序：

    PT15-12-42
PT15-12-572-575
PT15-3-11

    但是，您希望它们像这样排序：

    PT15-3-11
PT15-12-42
PT15-12-572-575

    根据您的预期结果，我会将所需的排序行为描述为“字符串由用破折号分隔的段组成。第一段是字母数字，每个后续段都是数字。排序应该由第一段完成，然后由第二段，然后是第三段，等等"

    要做到这一点，您需要将其分解ItemNameCode为单独的计算列，并在排序之前将数字段转换为整数。

    这种将字符串拆分为单独列的方法非常难看，但有效：

    SELECT ItemNameCode, c1.seg1, c2.seg2, c3.seg3, c4.seg4
FROM dbo.AlphaNumeric x
OUTER APPLY (SELECT CASE WHEN CHARINDEX('-',x.ItemNameCode,1) = 0 THEN x.ItemNameCode
                        ELSE SUBSTRING(x.ItemNameCode,1,CHARINDEX('-',x.ItemNameCode,1)-1) 
                    END AS seg1,
                    CHARINDEX('-',x.ItemNameCode,1)+1 AS pos1) c1
OUTER APPLY (SELECT CASE WHEN c1.pos1 = 1 THEN NULL
                        WHEN CHARINDEX('-',x.ItemNameCode,c1.pos1) = 0 THEN SUBSTRING(x.ItemNameCode,c1.pos1,8000) 
                        ELSE SUBSTRING(x.ItemNameCode,c1.pos1,CHARINDEX('-',x.ItemNameCode,c1.pos1)-(c1.pos1)) 
                    END AS seg2,
                    CASE WHEN c1.pos1 = 1 THEN 1
                        ELSE CHARINDEX('-',x.ItemNameCode,c1.pos1)+1 
                    END AS pos2) c2
OUTER APPLY (SELECT CASE WHEN c2.pos2 = 1 THEN NULL
                        WHEN CHARINDEX('-',x.ItemNameCode,c2.pos2) = 0 THEN SUBSTRING(x.ItemNameCode,c2.pos2,8000) 
                        ELSE SUBSTRING(x.ItemNameCode,c2.pos2,CHARINDEX('-',x.ItemNameCode,c2.pos2)-(c2.pos2)) 
                    END AS seg3,
                    CASE WHEN c2.pos2 = 1 THEN 1
                        ELSE CHARINDEX('-',x.ItemNameCode,c2.pos2)+1 
                    END AS pos3) c3
OUTER APPLY (SELECT CASE WHEN c3.pos3 = 1 THEN NULL
                        WHEN CHARINDEX('-',x.ItemNameCode,c3.pos3) = 0 THEN SUBSTRING(x.ItemNameCode,c3.pos3,8000) 
                        ELSE SUBSTRING(x.ItemNameCode,c3.pos3,CHARINDEX('-',x.ItemNameCode,c3.pos3)-(c3.pos3)) 
                    END AS seg4,
                    CASE WHEN c3.pos3 = 1 THEN 1
                        ELSE CHARINDEX('-',x.ItemNameCode,c3.pos3)+1 
                    END AS pos4) c4
ORDER BY c1.seg1, CAST(c2.seg2 AS INT), CAST(c3.seg3 AS INT), CAST(c4.seg4 AS INT)

    如果第 2、3、4 等值不是严格的数字，并且您还需要字母数字排序，则可以尝试ORDER BY在同一查询中使用此备用子句：

    ORDER BY c1.seg1, 
        RIGHT('00000' + COALESCE(c2.seg2,''),5),
        RIGHT('00000' + COALESCE(c3.seg3,''),5),
        RIGHT('00000' + COALESCE(c4.seg4,''),5)
    • 6

相关问题