环境创建的语句提供一下

报这个错

select t1.sal, t1.hiredate
  from scott.emp t1
  left join scott.emp t2
    on t2.hiredate > t1.hiredate
 group by t1.sal, t1.hiredate
having max(t2.sal) = t1.sal
复制

分析过程

--原sql
--语义分析，入司日期比"我"大的,且这些人里最高工资和"我"工资相等的那个人
--sql结构分析，where条件里使用了标量子查询，关联条件用了外面的表
select t1.sal,
t1.hiredate
from scott.emp t1
where t1.sal = (select max(sal) from scott.emp t2 where t2.hiredate > t1.hiredate);

--改写，将标量子查询移动到select后面,先忽略数据过滤条件，t1表查的是原数据，t1多少行，查出来就是多少行
select t1.sal,
t1.hiredate, (select max(sal) from scott.emp t2 where t2.hiredate > t1.hiredate) sal2
from scott.emp t1

--因此上面这个sql等价于下面这个，行数不会发生变化
select t1.sal,
t1.hiredate, (select max(sal) from scott.emp t2 where t2.hiredate > t1.hiredate) sal2
from scott.emp t1
group by t1.sal,t1.hiredate

--将标量子查询的表放到from后面去，就变成了
select t1.sal, t1.hiredate,max(t2.sal)
  from scott.emp t1
  left join scott.emp t2
    on t2.hiredate > t1.hiredate
 group by t1.sal, t1.hiredate

--最后用having过滤数据 
select t1.sal, t1.hiredate
  from scott.emp t1
  left join scott.emp t2
    on t2.hiredate > t1.hiredate
 group by t1.sal, t1.hiredate
having max(t2.sal) = t1.sal
复制

从语义上看，似乎是要找当前工资与晚于其入职人中的最高工资持平的人。如果是这样的话，利用分析函数，应该只访问目标表一次即可。

使用横向派生表，会让left join 改写更简单

with emp as
(
   select '张三' as ename, '2022-01-01'  as  hiredate,1000 as sal union all
   select '李四' as ename,'2022-02-01' as hiredate,2000 as sal union all
   select '王五' as ename, '2022-03-01'  as  hiredate,2000 as sal union all
   select '王二' as ename,'2022-04-01' as hiredate,1000 as sal union all
   select '王三' as ename,'2022-04-01' as hiredate,2000 as sal 
)
select t1.sal, t1.hiredate,t1.ename
from emp t1
left join LATERAL  (select max(sal)  as sal from emp t2 where t2.hiredate > t1.hiredate  ) as t3 on t1.sal = t3.sal  
where t3.sal is not null 
复制