SQL技术问询：按姓氏分组获取每组最短、最长名字的正确写法

原SQL的核心错误 #

1. 分组维度完全错误：需求是按last_name（姓氏）分组，但你写的是GROUP BY first_name，直接搞反了分组依据
2. 字段选择错误：用COUNT(last_name)来输出姓氏，这只会返回每个分组的记录数，而非姓氏本身，应该直接选择last_name字段
3. 别名格式非法：as last name包含空格，数据库会解析失败，需要用引号包裹（如as "姓氏"）或改用下划线形式
4. 逻辑偏差：MIN(first_name)和MAX(first_name)是按字符串字典序取值，不是按名字的字符长度筛选最短/最长名字，不符合需求

解决思路 #

要实现按姓氏分组，提取每组中字符长度最短和字符长度最长的名字，需先基于姓氏分组，再针对每组内的名字长度做排序筛选：

• 用窗口函数给每个姓氏分组内的名字按长度排序，标记出最短/最长的那条记录
• 或通过关联子查询，直接从每个姓氏分组中取出长度排序后的第一条记录

正确SQL写法 #

写法一：窗口函数（推荐，灵活处理多同长度场景）

WITH ranked_names AS (
    SELECT
        last_name AS 姓氏,
        first_name,
        -- 标记同姓氏分组中，长度最短的名字（长度升序，同长度取字典序最小）
        ROW_NUMBER() OVER (PARTITION BY last_name ORDER BY LENGTH(first_name) ASC, first_name ASC) AS rn_shortest,
        -- 标记同姓氏分组中，长度最长的名字（长度降序，同长度取字典序最大）
        ROW_NUMBER() OVER (PARTITION BY last_name ORDER BY LENGTH(first_name) DESC, first_name DESC) AS rn_longest
    FROM hr.employees
)
SELECT
    姓氏,
    MAX(CASE WHEN rn_shortest = 1 THEN first_name END) AS 最短名字,
    MAX(CASE WHEN rn_longest = 1 THEN first_name END) AS 最长名字
FROM ranked_names
GROUP BY 姓氏;

写法二：关联子查询（简洁，适合单条最短/最长的场景）

SELECT
    DISTINCT e.last_name AS 姓氏,
    -- 取同姓氏中长度最短的名字（同长度取字典序最小）
    (SELECT first_name 
     FROM hr.employees 
     WHERE last_name = e.last_name 
     ORDER BY LENGTH(first_name) ASC, first_name ASC 
     LIMIT 1) AS 最短名字,
    -- 取同姓氏中长度最长的名字（同长度取字典序最大）
    (SELECT first_name 
     FROM hr.employees 
     WHERE last_name = e.last_name 
     ORDER BY LENGTH(first_name) DESC, first_name DESC 
     LIMIT 1) AS 最长名字
FROM hr.employees e;

补充说明 #

如果你的需求里的"最短/最长"实际指字符串字典序的最小/最大（而非字符长度），可以简化为以下写法（但不符合示例输出逻辑）：

SELECT
    last_name AS 姓氏,
    MIN(first_name) AS 最短名字,
    MAX(first_name) AS 最长名字
FROM hr.employees
GROUP BY last_name;

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