SQL多表关联取分组首行时CTE、窗口函数报错如何解决？

报错根因 #

1. CTE语法错误：你使用的SQL Server不支持将CTE（公共表表达式）嵌套在FROM子句的子查询中，CTE必须声明在整段查询的最开头。你单独运行CTE片段无报错，就是因为此时CTE位置符合语法要求。
2. 窗口函数别名引用错误：SQL执行顺序中WHERE子句优先级高于SELECT子句，窗口函数是在SELECT阶段才计算生成的，因此同层级WHERE子句无法直接引用SELECT中定义的别名，就会提示visitnum列不存在。

修复方案（沿用高效的窗口函数逻辑，无需临时表） #

两种写法执行效率完全一致，都可以满足需求：

方案1：CTE前置写法（保留你原来的CTE实现思路） #

WITH added_row_number AS (
    SELECT 
        encounter_id, 
        patient_id, 
        start_dts,
        ROW_NUMBER() OVER(PARTITION BY patient_id ORDER BY start_dts) AS row_number
    FROM encounters
)
SELECT 
    p.patient_nm AS 'Patient Name', 
    d.code AS 'Diagnosis Code', 
    CONVERT(DATE, e.start_dts) AS 'Encounter Date'
FROM added_row_number e
LEFT JOIN edw_emr_ods.patients p
    ON e.patient_id = p.patient_id
LEFT JOIN edw_emr_ods.encounter_diagnoses ed
    ON e.encounter_id = ed.encounter_id
LEFT JOIN edw_emr_ods.diagnoses d
    ON ed.diagnosis_id = d.diagnosis_id
WHERE e.row_number = 1

方案2：嵌套子查询写法 #

SELECT 
    p.patient_nm AS 'Patient Name', 
    d.code AS 'Diagnosis Code', 
    CONVERT(DATE, e.start_dts) AS 'Encounter Date'
FROM (
    SELECT 
        encounter_id, 
        patient_id, 
        start_dts,
        ROW_NUMBER() OVER(PARTITION BY patient_id ORDER BY start_dts) AS visitnum
    FROM edw_emr_ods.encounters
) e
LEFT JOIN edw_emr_ods.patients p
    ON e.patient_id = p.patient_id
LEFT JOIN edw_emr_ods.encounter_diagnoses ed
    ON e.encounter_id = ed.encounter_id
LEFT JOIN edw_emr_ods.diagnoses d
    ON ed.diagnosis_id = d.diagnosis_id
WHERE e.visitnum = 1

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