SQL使用where子句在select语句上保持连接

I am trying to make an left join on a select statement like this:

我试图在这样的选择语句上进行左连接:

select *
from (select * from foo  where rownum <= 10 ) tab1
left join (select sum(total) total,sum(worker) worker from bars  where work_date between tab1.start_date and tab1.end_date ) tab2
on tab1.foo_id=tab2.id

and I get the following error:

我收到以下错误:

oci_execute(): ORA-00904: "tab1"."end_date": invalid identifier

oci_execute():ORA-00904:“tab1”。“end_date”:标识符无效

Any help would be appreciated, thank you!

任何帮助将不胜感激,谢谢!

2 个解决方案

#1

One way to do what you want uses two correlated subqueries:

执行所需操作的一种方法是使用两个相关的子查询:

select foo.*
     (select sum(b.total) as total, sum(worker) as worker
      from bars
      where b.work_date between foo.start_date and foo.end_date and
            foo.foo_id = b.id
     ) as total,
     (select sum(b.worker) as worker
      from bars b
      where b.work_date between foo.start_date and foo.end_date and
            foo.foo_id = b.id
     ) as worker
from foo
where rownum <= 10;

#2

This should also work:

这应该也有效:

select
   tab1.foo_id
  ,tab1.start_date
  ,tab1.end_date
  ,sum(tab2.total) as total
  ,sum(tab2.worker) as worker
from foo tab1
left join bars tab2
  on tab2.work_date between tab1.start_date and tab1.end_date
  and tab1.foo_id = tab2.id
where tab1.rownum <= 10
group by
   tab1.foo_id
  ,tab1.start_date
  ,tab1.end_date

#1