从具有相同ID的两个表中左联接

Question

我有带数据的表1e1和表2
表1

location    costA
a           5
a           10
a           15
b           11
b           12

表2

Location    CostB
a          100
b          100

我的目标是得到的结果

location    costA   costB
a          5       100
a          10       
a          15       
b          11      50
b          12       

我的查询

select T1.location, T1.cost
from (
select location, cost
, row_number() over ( partition by location order by cost) rownumber
from table1
) T1 left join (
select location, cost
, row_number() over ( partition by location order by cost ) rownumber
from table2
) T2 on T2.location = T2.cost and T1.rownumber = T2.rownumber

我有

location    costA    missing costB column
a           5
a           10
a           15
b           11
b           12  

不知道为什么，但你能指出缺失的一个吗。非常感谢。

Answer 1

首先，您期望结果中有三列，而您的select语句只包含2列。

select T1.Location, T1.Cost

第二个连接应该是

T2 on T1.[location] = T2.[location] and T1.rownumber = T2.rownumber

以下是的完整工作示例

DECLARE @table1 as table
(
[location] char,
costA int
)
DECLARE @table2 as table
(
[location] char,
costB int
)
INSERT INTO @table1
VALUES
('a', 5)
,('a', 10)
,('a', 15)
,('b', 11)
,('b', 12)

INSERT INTO @table2
VALUES
('a', 100)
,('b', 100)
select T1.[location], T1.costA, T2.costB
from (
select [location], costA 
, row_number() over ( partition by location order by costA) rownumber
from @table1
) T1 left join (
select [location], costB
, row_number() over ( partition by location order by costB ) rownumber
from @table2
) T2 on T1.[location] = T2.[location] and T1.rownumber = T2.rownumber

Answer 2

加入

T2 on T2.location = T2.cost and T1.rownumber = T2.rownumber

应该在上

T2 on T1.location = T2.location and T1.rownumber = T2.rownumber

select 
T1.location, 
T1.costA,
T2.costB
from (
select 
location, 
costA,
row_number() over ( partition by location order by costA) rownumber
from table1
) T1 
left join (
select 
location, 
costB, 
row_number() over ( partition by location order by costB ) rownumber
from table2
) T2 
on T1.location = T2.location and T1.rownumber = T2.rownumber
GO

位置|costA|costB：-------|----：|----：a|5|100a|10|nulla|15|nullb|11|100b|12|null

db<gt；小提琴这里