sql-server - 对多个连接的错误进行故障排除，其中一个表具有 group-by 语句中的变量的最小值

Question

我有一个 SQL 用户考试的练习题，要求我从四个不同的表的组合中返回三列，但我只及时返回分组语句中的患者 ID 的第一行。

这是我所拥有的：

select p.patient_nm as 'Patient Name', d.code as 'Diagnosis Code', convert(date, e.start_dts) as 'Encounter Date'
from
(with added_row_number as (select *, row_number() over(partition by patient_id order by start_dts) as row_number
from encounters
)
select encounter_id, patient_id, start_dts
from added_row_number 
where row_number=1) 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

这部分代码似乎有问题，当我搜索有关如何仅返回“分组依据”组中的第一行的信息时，我从其他网站获得并改编了该代码

with added_row_number as (select *, row_number() over(partition by patient_id order by start_dts) as row_number
from encounters
)
select encounter_id, patient_id, start_dts
from added_row_number 
where row_number=1

当我只运行这一部分时，我没有收到任何错误，它返回了加入其他三个表所需的最低限度，因此我可以返回三个必需的变量。这是我只运行那部分时的样子：

但这些是我在运行整个过程时遇到的错误：

Msg 156, Level 15, State 1, Line 280
Incorrect syntax near the keyword 'with'.
Msg 319, Level 15, State 1, Line 280
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 285
Incorrect syntax near ')'.

我试过插入一个单独的分号和几个不同的地方，但它们都不起作用。

此代码似乎应该是返回此表的计算效率最高的方法之一。

如果这不是要走的路，请让我知道最好的代码是什么，最好不使用临时表。

谢谢

编辑： 根据评论，我正在尝试将那段较小的代码更改为子查询，但仍然出现错误：

(select encounter_id, patient_id, start_dts, row_number() over(partition by patient_id order by start_dts) visitnum
from edw_emr_ods.encounters
where visitnum=1) e

产生错误：

Msg 207, Level 16, State 1, Line 298
Invalid column name 'visitnum'.

如果我省略 where 子句，子查询可以工作，但它会返回所有行，而我只需要 'visitnum' 等于 1 的那些行。

标签： sql-serverjoingroup-by

Answer 1

with是公用表表达式，不能在子查询中使用。只需将其转换为常规子查询即可。

并且您不能在与创建它相同的上下文中引用列别名，除了在order by. 所以你需要一个额外的子查询/派生表。

(
  select encounter_id, patient_id, start_dts
  from (
    select encounter_id, patient_id, start_dts, row_number() over (partition by patient_id order by start_dts) visitnum
    from edw_emr_ods.encounters
  ) e1
  where visitnum = 1
) e