使用ROW_NUMBER()函数分组数据时遭遇列名无效错误求助

SQL的执行顺序是先处理WHERE子句，再执行SELECT子句。你在SELECT里定义的列别名dstbadge，在WHERE阶段还没被创建，所以数据库找不到这个列，就会报“Invalid column name 'dstbadge'”错误。

你需要先完成排名计算，再过滤排名为1的行，常用两种实现方式：

方法1：使用CTE（公共表表达式） #

WITH RankedAccess AS (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY Access.Number ORDER BY Access.Time) AS dstbadge,
        Access.Event AS 'Event',
        Access.Reader AS 'Reader',
        Access.Time AS 'Event Time UTC',
        Access.LastName AS 'Last',
        Access.FirstName AS 'First',
        Access.Number AS 'EmpNum'
    FROM Access
)
SELECT *
FROM RankedAccess
WHERE dstbadge = 1;

方法2：使用子查询 #

SELECT *
FROM (
    SELECT
        ROW_NUMBER() OVER (PARTITION BY Access.Number ORDER BY Access.Time) AS dstbadge,
        Access.Event AS 'Event',
        Access.Reader AS 'Reader',
        Access.Time AS 'Event Time UTC',
        Access.LastName AS 'Last',
        Access.FirstName AS 'First',
        Access.Number AS 'EmpNum'
    FROM Access
) AS RankedAccess
WHERE dstbadge = 1;

这两种写法都会先给每条数据按Access.Number分组并按Access.Time排序生成排名，再筛选出每组排名第一的行，正好满足你的需求。

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