创建多表关联视图：无需GROUP BY去重并获取学生最高尝试次数

现有表结构与数据 #

现有Student表结构为(id, attempt)，表中数据如下：

需求说明 #

在不使用GROUP BY与WHERE语句的前提下，消除重复行并获取每个学生的最高attempt值，期望输出结果：

用户尝试的错误语句 #

用户当前尝试的关联语句存在语法错误（重复ON条件、表名不匹配等），原语句如下：

INNER JOIN (SELECT id, MAX(attempts)as MaxCreated
                FROM SHRTCKG
                GROUP BY id, attempts) u
        ON x.id = u.attempts
        AND x.id = u.attempts)

解决方案 #

方法一：窗口函数+DISTINCT #

利用MAX()窗口函数计算每个id对应的最大attempt值，再通过DISTINCT去重，无需GROUP BY和WHERE：

SELECT DISTINCT 
    id, 
    MAX(attempt) OVER (PARTITION BY id) AS attempt
FROM Student;

方法二：QUALIFY子句（适用于BigQuery、Snowflake等支持该语法的数据库） #

通过ROW_NUMBER()窗口函数按id分组并按attempt降序排序，用QUALIFY筛选每组的第一行：

SELECT id, attempt
FROM Student
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY attempt DESC) = 1;

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