SQL 组合 2 选择语句以从两行获取数据

Question

我有一个表，每条记录包含 2 行。 初始记录，然后记录结束时，它们包含相同的信息，除了第二行上的一列具有已完成的位置。

eg 1st row 
Id, New ID, RecordType, Customer Name, Customer Address, Created,   Closed,   location 
1 , Null,   Initial,     John Smith,    1, long lane,   01/05/2019, 10/05/2019, Office
2nd row
Id, New ID, RecordType,  Customer Name, Customer Address, Created, Closed,    Location
2 , 1,     Completed,     John Smith,    1, long lane,   01/05/2019, 10/05/2019, Field

我想报告第一行，但我想从第二行开始的位置，第二行通过 ID 和新 ID 字段与第一行链接。

我尝试使用 CTE 查询，但返回的结果少于仅查询第一行时返回的结果。

WITH group1 AS (select * from a where closed is not null),
group2 AS (select location from a where closed is not null)
SELECT *
FROM group1
JOIN group2 ON group1.ID = group2.NewID ;

我希望这是有道理的，只是想得到一些关于如何连接这 2 行的建议

Answer 1

似乎需要左连接

WITH group1 AS (select * from a where closed is not null),
group2 AS (select location from a where closed is not null)
SELECT group1.*,group2.location
FROM group1
left JOIN group2 ON group1.ID = group2.NewID

即使你不需要CTE，你也可以使用selft加入

select t1.*,t2.location
from a t1 left join a t2 on t1.ID = t2.NewID
where t1.closed is not null

Answer 2

您可以使用这可能会有所帮助。

select s1.*, s2.location as newlocation
FROM tablename as s1
inner join 
tablename as s2
on
s1.Id=s2.new_id

您可以将表与自身联接

Answer 3

您需要表的左自连接：

select  
a1.id, a2.newid, a2.recordtype,
a1.customername, a1.customeraddress,
a1.created, a1.closed, a2.location
from a a1 left join a a2
on a2.newid = a1.id 
where a1.newid is null

Answer 4

没有 CTE 的正确逻辑是：

select t1.*, t2.location
from a t1 left join
a t2
on t2.NewID = t1.ID and
t2.closed is not null
where t1.closed is not null