将两个不同的查询结果添加到一个表中 [英] add two different queries result into one table

问题描述

我有两个不同的查询(结果中的列数相同).我想把它们放在一张桌子上.

I have two different query (having same no. of columns in result). I want to put both in one table.

例如我有下表:

id     country     salary 
1        us        10000

2        uk        25000

3        us        35000

4        uk        31000

5        uk        26000

现在我有以下疑问:

查询 1:

select * from table where country='us';

和

查询 2:

select * from table where country='uk';

我有一张有六列的决赛桌，例如:

i have one final table having six columns like:

 id1   |country1  |  salary 1 |  id2  |  country2 |  salary2

现在，我想将两个查询结果都放在此表中，因此应显示以下输出:

Now, i want to put both queries result in this table so following output should be shown:

期望的输出:

id1   |country1  |  salary 1  |  id2   |  country2 |  salary2 
 1    |     us   |   10000    |    2   |      uk   |   25000

 3    |     us   |   35000    |    4   |      uk   |   31000

null  |   null   |   null     |    5   |      uk   |   26000

我试过这个，但它没有结合结果:

I have tried this but it doesn't combine the result:

insert into table (id1,country1,salary1)
select id,country,salary
from table1
where country='us';

和

insert into table (id2,country2,salary2)
select id,country,salary
from table1
where country='uk';

但它给出了以下结果:

id1   |country1    |  salary 1  |  id2      |  country2   |  salary2 
 1    |     us     |     10000  |     null  |     null    |     null

3     |    us      |    35000   |    null   |    null     |    null

null  |   null     |   null     |   2       |    uk       |    25000

null  |   null     |   null     |   4       |    uk       |    31000

null  |   null     |   null     |   5       |    uk       |    26000

请帮帮我:

推荐答案

如果您的 DBMS 支持窗口函数，您可以使用它们适当地加入您的中间结果.

If your DBMS support window functions, you may use them to join your intermediate result appropriately.

select t1.id, t1.country, t1.salary, t2.id, t2.country, t2.salary
from
(
  select *, row_number() over (order by id) rn
  from data
  where country = 'us'
) t1
full join
(
  select *, row_number() over (order by id) rn
  from data
  where country = 'uk'
) t2 on t1.rn = t2.rn

演示

结果

id      country salary  id  country salary
-------------------------------------------
1       us      10000   2   uk      25000
3       us      35000   4   uk      31000
null    null    null    5   uk      26000

这篇关于将两个不同的查询结果添加到一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！