SQL嵌套联合

我有两个带有一些 ID 的表，我需要一个返回以下内容的查询:

new_bet_id  old_bet_id  new_id  old_id
110         120         1       2
130         140         3       4
150         160         5       6

我没有问题让 new_bet_id 正确，它的 old_bet_id 我有问题，因为它来自同一列。 new_id 将与 new_bet_id 映射， old_id 将与 old_bet_id 映射

到目前为止，我已经尝试了以下方法:

SELECT
one.bet_id as new_bet_id,
null as old_bet_id,
two.new_id,
two.old_id

FROM test_table_two two
JOIN test_table_one one ON one.kund_id = two.new_id

UNION

SELECT
null  as new_bet_id,
one.bet_id as old_bet_id,
two.new_id,
two.old_id

FROM test_table_two two
JOIN test_table_one one ON one.kund_id = two.old_id

但结果并不是我真正想要的:

new_bet_id  old_bet_id  new_id  old_id
110         null        1       2
130         null        3       4
150         null        5       6
null        120         1       2
null        140         3       4
null        160         5       6

以下是此示例的数据库设置

CREATE TABLE test_table_one
(bet_id int,
kund_id int);

CREATE TABLE test_table_two
(new_id int,
old_id int);

insert into test_table_one values(110,1);
insert into test_table_one values(120,2);
insert into test_table_one values(130,3);
insert into test_table_one values(140,4);
insert into test_table_one values(150,5);
insert into test_table_one values(160,6);

insert into test_table_two values(1,2);
insert into test_table_two values(3,4);
insert into test_table_two values(5,6);

希望有人能解决这个问题，谢谢

最佳答案

UNION 为您提供更多行。如果您不想要更多行，那么它就是错误的工具。

SELECT
  oneA.bet_id as new_bet_id,
  oneB.bet_id as old_bet_id,
  two.new_id,
  two.old_id
FROM test_table_two two
  JOIN test_table_one oneA ON two.new_id = oneA.kund_id
  JOIN test_table_one oneB ON two.old_id = oneB.kund_id

关于SQL嵌套联合，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/6425577/