如何在SQL Server中按自定义规则排序混合类型商品尺码字段

步骤1：创建非数值类尺码排序参考表 #

你已经手动梳理好了所有非数值类尺码的排序规则，直接新建一张映射表存储自定义优先级即可，表结构和插入示例如下：

CREATE TABLE size_custom_sort (
    size_text VARCHAR(50) PRIMARY KEY,
    sort_priority INT NOT NULL UNIQUE
);

-- 按你手动整理的顺序依次插入，sort_priority值越小排序越靠前
INSERT INTO size_custom_sort(size_text, sort_priority) VALUES
('XXS',1),
('XS',2),
('S',3),
('M',4),
('L',5),
-- 后续按你整理的顺序插入所有非数值类尺码即可，数值类尺码无需插入
('156-166', 99);

步骤2：实现带分数数值尺码的转换函数 #

ISNUMERIC不识别28 1/2这类带空格和分数的字符串是正常的，你可以写一个自定义转换函数，把这类格式统一转为十进制数值：

CREATE FUNCTION dbo.SizeToNum (@size VARCHAR(50))
RETURNS DECIMAL(8,2)
AS
BEGIN
    DECLARE @num DECIMAL(8,2)
    -- 处理纯数字、小数格式
    IF CHARINDEX(' ', @size) = 0 AND CHARINDEX('/', @size) = 0
        SET @num = TRY_CAST(@size AS DECIMAL(8,2))
    -- 处理带分数的格式
    ELSE
    BEGIN
        DECLARE @int_part VARCHAR(20) = LEFT(@size, CHARINDEX(' ', @size)-1)
        DECLARE @frac VARCHAR(20) = STUFF(@size, 1, CHARINDEX(' ', @size), '')
        DECLARE @m INT = LEFT(@frac, CHARINDEX('/', @frac)-1)
        DECLARE @n INT = STUFF(@frac, 1, CHARINDEX('/', @frac), '')
        SET @num = CAST(@int_part AS DECIMAL(8,2)) + CAST(@m AS DECIMAL(8,2))/CAST(@n AS DECIMAL(8,2))
    END
    RETURN @num
END

步骤3：最终排序逻辑 #

查询时按如下规则写ORDER BY即可实现需求：

SELECT p.* 
FROM products p
LEFT JOIN size_custom_sort s ON p.size = s.size_text
ORDER BY
    p.product_name ASC, -- 第一优先级：商品名称排序
    -- 第二优先级：非数值类统一排在数值类前/后，可调整0和1的顺序修改先后
    CASE WHEN s.sort_priority IS NOT NULL THEN 0 ELSE 1 END ASC,
    -- 第三优先级：非数值类按自定义优先级排序
    s.sort_priority ASC,
    -- 第四优先级：数值类转换为十进制后升序
    dbo.SizeToNum(p.size) ASC

如果不想单独建函数，也可以把转换逻辑直接写在ORDER BY子句中，函数的优势是后续新增数值类尺码无需调整排序逻辑，维护成本更低。

内容的提问来源于stack exchange，提问作者Faye D.