在Where子句中使用子查询结果

Question

为什么我不能在以下sql中描述的where子句中使用子查询子句结果：

with rpt as(
 select * from reports where caseid = 
 :case_id and rownum=1 order by created desc
)
select 
 distinct rt.trialid
from 
 report_trials rt
join 
 trial_genes tg on rt.id=tg.trialid
where 
 rt.reportid = rpt.id
and 
tg.gene not in('TMB','MS')

该子查询命名为rpt，并在Select语句的WHERE子句中使用。执行时遇到以下错误：ORA-00904: "RPT"."ID": invalid identifier

更新：

实际上对同一件事的嵌套查询也给了我同样的问题。嵌套子查询仅从一行返回单列值：

select 
 distinct rt.trialid
from 
  report_trials rt
  join 
  trial_genes tg on rt.id=tg.trialid
where 
 rt.reportid = (select id from reports where caseid = :case_id and 
  rownum=1 order by created desc)
and 
 tg.gene not in('TMB','MS')

Answer 1

您错过了在查询中添加表rpt的表格，因此该错误。

with rpt as(
 select * from reports where caseid = 
 :case_id and rownum=1 order by created desc
)
select 
 distinct rt.trialid
from 
 report_trials rt
join 
 trial_genes tg on rt.id=tg.trialid
join 
  rpt on rt.reportid = rpt.id
where  
  tg.gene not in('TMB','MS')