从两个不同的选择语句中获得两行 [英] get two rows from two different select statements

问题描述

我有一个类似下面的表：
table_a

I have a table something like below: table_a

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

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

I have two select statements ans there results as below:

首先选择

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

感谢Praveen的Gorgon。我用两种方式都得到结果。

Thanks Gorgon, Praveen. I get results in both ways.

我还有另一个表，如下：

I have another table as below:

table_b

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

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

when I try to update table_b with the below query:

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

我正在获取结果

table_b

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

如何获得以下结果
table_b

how can I get the below results table_b

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

谢谢

推荐答案

一种方法使用全部联合：

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;

另一个仅使用情况：

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);

这篇关于从两个不同的选择语句中获得两行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！