sql - 从两个不同的选择语句中获取两行

Question

我有一个类似下面的表格:表_a

  k | id | results 
--------------------
  a | 1  | mango
  b | 1  | orange
  c | 2  | apple
  d | 2  | banana
  a | 2  | mango

我有两个选择语句，结果如下:

先选择

select k, id, results from 
table_a where id = 1

结果:

  k | id | results 
--------------------
  a | 1  | mango
  b | 1  | orange

第二次选择

select k, id, results from 
table_a where id = 2

结果:

  k | id | results 
--------------------
  c | 2  | apple
  d | 2  | banana
  a | 2  | mango

我怎样才能得到如下结果:

  k | id |  abc  | xyz
------------------------
  a | 1  | mango | mango
  b | 1  | orange| xx
  c | 2  | xx    | apple
  d | 2  | xx    | banana

谢谢 Gorgon，Praveen。我通过两种方式获得结果。

我有另一个表格如下:

表_b

  k | 1  | 2 
--------------------
  a |    | 
  b |    | 
  c |    | 
  d |    | 

当我尝试使用以下查询更新 table_b 时:

update table_b set 
abc = x.abc, xyz = x.xyz from (
select k, id, result as abc, 'xx' as xyz 
from table_a
where id = 1
union all
select k, id, 'xx' as abc, result as xyz
from table_a
where id = 2 ) x
where table_b.k = x.k

我得到的结果如下:

表_b

  k |  1    | 2 
--------------------
  a | xx    | mango
  b | orange| xx
  c | xx    | apple
  d | xx    | banana

我怎样才能得到下面的结果表_b

  k |  1    | 2 
--------------------
  a | mango | mango
  b | orange| xx
  c | xx    | apple
  d | xx    | banana

谢谢，

Answer 1

一种方法使用union all:

select k, id, results as abc, 'xx' as xyz 
from table_a
where id = 1
union all
select k, id, 'xx' as abc, results as xyz
from table_a
where id = 2;

另一个只是使用case:

select i, id,
       (case when id = 1 then results else 'xx' end) as abc,
       (case when id = 2 then results else 'xx' end) as xyz
from table_a
where id in (1, 2);