如何在WITH AS之后实现左外连接子句?

Question

目前试图找出如何实现SQL左外连接，而使用SQL WITH AS子句。我的代码在使用同一表时分解为3个SELECT语句，然后使用LEFT OUTER JOIN合并id上的另一个表。

在加入之前我需要3个SELECT语句，因为我需要一个SELECT语句来抓取所需的列，ROW RANK时间，并为ROW RANK设置WHERE子句。

SELECT *
(
WITH employee AS
(
SELECT id, name, department, code, time, reporttime, scheduled_time
FROM table1 AS a
WHERE department = "END"
),
employe_v2 as 
(
SELECT address
,ROW_NUMBER() OVER (PARTITION BY id ORDER BY time desc, reporttime desc, scheduled_time desc) AS row_rank 
FROM table1 AS b
)
SELECT *
FROM employee, employee_v2
WHERE row_rank = 1
) t1
LEFT OUTER JOIN 
(
SELECT b.id, b.new_code, b.date
FROM table2 AS b
WHERE b.newcode != "A" 
) t2
ON t1.id = t2.id
Group BY t1.id, t1.name, t1.department, t1.code, t1.time, t1.reporttime,
t1.scheduled_time, t1.row_rank, t2.id, t2.new_code, t2.date

如何修改我的代码?

Answer 1

不确定是否需要分组，我没有看到聚合什么的
但是如果你需要的话，你可以在最后选择的末尾添加当然你必须注意选择

中的列/聚合然而，你可以简化你的查询如下:

with employee as (
select * from (
select id, name, department, code, time, reporttime, scheduled_time, address
,row_number() over (partition by id order by time desc, reporttime desc, scheduled_time desc) AS row_rank 
from table1
) t where row_rank =1 
)
select t1.*, b.id, b.new_code, b.date
from employee t1
left join table2 as t2
on t1.id = t2.id
where t2.newcode != "A"