时间戳

首页 > 解决方案 > SQL Combine 2 Select 语句从两行获取数据

sql - SQL Combine 2 Select 语句从两行获取数据

问题描述

我有一个表，每条记录包含 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 行的建议

标签： sqlcommon-table-expressionsql-server-2017

解决方案

看来你需要左加入

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 join

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

推荐阅读