JOIN关联含row_number的子查询时row字段致无结果，求解决

我运行以下SQL代码时无法获取结果，但如果从第一个SELECT语句中移除b.row字段，就能得到预期的结果集。我最终希望在结果集中添加一列，用于标识DIM表子查询返回的每一行的排名/顺序。

Select b.row, a.id, b.year, b.month, b.max_week, a.Sector, a.Item
from FACT a
 join   
    (Select top 12 row_number() over (order by year asc, month asc) as row,
    year, month, max(week) max_week, max(id) max_id
        from DIM  
        group by year, month ) b
on a.id = b.max_id
and Sector <> '3101' 

结果集：

解决方法 #

问题原因 #

row是多数关系型数据库的保留关键字，直接将其用作字段别名会导致数据库解析SQL时出现异常，进而无法返回结果。

修正方案 #

有两种可行的解决方式：

方式1：更换非保留字作为别名

将子查询中的row别名替换为rn、row_num这类非关键字名称，示例代码如下：

Select b.rn, a.id, b.year, b.month, b.max_week, a.Sector, a.Item
from FACT a
join (
    Select top 12 
        row_number() over (order by year asc, month asc) as rn,
        year, month, max(week) max_week, max(id) max_id
    from DIM  
    group by year, month 
) b on a.id = b.max_id
where a.Sector <> '3101'

方式2：用符号包裹保留字别名

如果坚持使用row作为别名，可根据数据库类型用对应符号包裹：

• SQL Server：用方括号[row]
• MySQL/PostgreSQL：用双引号"row"

示例（以SQL Server为例）：

Select b.[row], a.id, b.year, b.month, b.max_week, a.Sector, a.Item
from FACT a
join (
    Select top 12 
        row_number() over (order by year asc, month asc) as [row],
        year, month, max(week) max_week, max(id) max_id
    from DIM  
    group by year, month 
) b on a.id = b.max_id
where a.Sector <> '3101'

额外建议 #

将Sector <> '3101'从JOIN的ON条件移到WHERE子句中，逻辑更清晰——这是对FACT表的过滤条件，而非两张表的关联条件（INNER JOIN下两者效果一致，但可读性更强）。

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