如何在SQL的ORDER BY CASE语句中结合LIKE实现部分文本搜索？

问题场景 #

有一张Personnel表，其中Rank列存储员工的职位名称+后续认证等级，示例值如下：

• Captain Paramedic
• Captain Intermediate
• Captain EMT
• Lieutenant Paramedic
• Lieutenant Intermediate
• Lieutenant EMT
• Apparatus Operator Paramedic
• Firefighter EMT
• Firefighter AEMT

需要按**职位前缀（比如Captain、Lieutenant）**对查询结果排序，忽略后面的认证等级，避免枚举所有可能的完整Rank值。

当前繁琐的可行写法 #

之前需要枚举所有完整的Rank值才能实现排序，写法非常繁琐：

SELECT 
    p.Rank
FROM Personnel p
ORDER BY 
    CASE p.Rank
        WHEN 'Captain Paramedic' THEN 1
        WHEN 'Captain EMT' THEN 1
        WHEN 'Lieutenant Paramedic' THEN 2
        WHEN 'Lieutenant EMT' THEN 2
        ELSE 3
    END

错误尝试 #

尝试直接在CASE [列名] WHEN结构中使用LIKE，但这种语法不被SQL支持：

-- 此写法无效
SELECT 
    p.Rank
FROM Personnel p
ORDER BY 
    CASE p.Rank
        WHEN LIKE 'Captain%' THEN 1
        WHEN LIKE 'Lieutenant%' THEN 2
        ELSE 3
    END

解决方案 #

方法一：调整CASE语句结构（最直接） #

把LIKE条件放在WHEN子句中，改用CASE WHEN [条件] THEN的结构，就能实现前缀匹配排序：

SELECT 
    p.Rank
FROM Personnel p
ORDER BY 
    CASE
        WHEN p.Rank LIKE 'Captain%' THEN 1
        WHEN p.Rank LIKE 'Lieutenant%' THEN 2
        ELSE 3
    END,
    p.Rank -- 可选：同一优先级内按完整Rank值排序

这种写法不用枚举所有完整值，直接通过前缀匹配划分排序优先级，简洁高效。

方法二：提取职位前缀排序（适合前缀为固定单词） #

如果职位前缀都是单个单词（比如Captain、Lieutenant），可以用字符串函数提取前缀后再排序，不同数据库的函数略有差异：

• MySQL/MariaDB示例：

SELECT 
    p.Rank
FROM Personnel p
ORDER BY 
    CASE SUBSTRING_INDEX(p.Rank, ' ', 1)
        WHEN 'Captain' THEN 1
        WHEN 'Lieutenant' THEN 2
        ELSE 3
    END,
    p.Rank

• SQL Server示例：

SELECT 
    p.Rank
FROM Personnel p
ORDER BY 
    CASE LEFT(p.Rank, CHARINDEX(' ', p.Rank) - 1)
        WHEN 'Captain' THEN 1
        WHEN 'Lieutenant' THEN 2
        ELSE 3
    END,
    p.Rank

方法三：用映射表维护排序优先级（适合多职位场景） #

如果职位类型较多且后续可能变动，可以创建一个排序优先级映射表，后续维护更方便：

1. 创建映射表：

CREATE TABLE RankPriority (
    RankPrefix VARCHAR(50),
    SortOrder INT
);

INSERT INTO RankPriority VALUES
('Captain', 1),
('Lieutenant', 2),
('Apparatus Operator', 3),
('Firefighter', 4);

2. 关联查询排序：

SELECT 
    p.Rank
FROM Personnel p
LEFT JOIN RankPriority rp ON p.Rank LIKE CONCAT(rp.RankPrefix, '%')
ORDER BY 
    COALESCE(rp.SortOrder, 99), -- 未匹配的职位排在最后
    p.Rank

内容的提问来源于stack exchange，提问作者SQL Nerd