如何移除CTE中ROW_NUMBER生成的rn列？SQL技术求助

修改后的完整SQL代码 #

-- Fixed SQL Edition (Dairy XL)
with cte as
(
SELECT  
    LelyCenter.LceName as 'Lely Center Name',
    damstaticfarmdata.sfdcustomermovexcode as 'Customer Movex Code',
    damstaticfarmdata.sfdfarmname as 'Farm Name',
    TskOsAndSqlCheck.toasOsInfo as 'Windows Version',
    TskOsAndSqlCheck.toasSqlVersion as 'SQL Version',
    TskOsAndSqlCheck.toasSqlDatabaseSizeInMB as 'Database Size (MB)',
    damstaticfarmdata.sfdnrrobots as 'Nr of Robots',
    TskOsAndSqlCheck.toasTime as 'BM Last Upload Time',
    ROW_NUMBER() OVER (PARTITION BY sfdcustomermovexcode ORDER BY toasTime DESC) AS rn
FROM            
    LelyCenter 
INNER JOIN damstaticfarmdata 
    ON LelyCenter.LceMovexCode = damstaticfarmdata.sfdlelycentercode 
INNER JOIN TskFarmBasic 
    ON damstaticfarmdata.sfdcustomermovexcode = TskFarmBasic.FrmCustomerMovexCode 
INNER JOIN TskOsAndSqlCheck 
    ON TskFarmBasic.FrmId = TskOsAndSqlCheck.toasFrmId
WHERE 
    FrmCountry in ('CA', 'US') 
    and sfdnrrobots > 7 
    and toasSqlVersion like '%Express%' 
    and toasTime > '2023-01-01'
)
select 
    [Lely Center Name],
    [Customer Movex Code],
    [Farm Name],
    [Windows Version],
    [SQL Version],
    [Database Size (MB)],
    [Nr of Robots],
    [BM Last Upload Time]
from cte 
where rn = 1 
Order by  [Lely Center Name] asc, [Farm Name] asc

关键修改说明 #

• 移除rn列：外层查询放弃使用SELECT *，而是明确列出CTE中所有需要展示的列（排除用于过滤的rn），确保最终结果集不再包含该列。
• 解决ORDER BY报错：原语句用单引号包裹列名，SQL会将其识别为字符串常量而非列引用，触发Msg 408错误。改用方括号[]包裹带空格的列名，让SQL正确识别排序字段。
• 规范语法：给CTE中toasSqlDatabaseSizeInMB的别名定义补上as关键字，避免潜在的语法歧义。

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