sql - 在同一个表 PostgreSql 上加入多个选择查询

Question

下面是我得到的示例表结构，

C1  C2  C3 C4
A   D   G  X
B   E   H  X
C   F   I  X

select C2 as 1_C2, C3 as 1_C3 from table1 where C1 = A and C4=X
select C2 as 2_C2, C3 as 2_C3 from table1 where C1 = B and C4=X
select C2 as 3_C2, C3 as 3_C3 from table1 where C1 = C and C4=X

以上就是我得到的三个select语句。现在我需要加入所有三个 select 语句并只获得一行作为输出，

1_C2 2_C2 3_C2 1_C3 2_C3 3_C3    
   D    E    F    G    H    I

看到了多个其他帖子，但不符合此要求。非常感谢任何帮助。

Answer 1

您可以使用 CASE 表达式，结合 MAX():

select MAX(CASE WHEN C1 = 'A' THEN C2 END) as 1_C2,
       MAX(CASE WHEN C1 = 'B' THEN C2 END) as 2_C2,
       MAX(CASE WHEN C1 = 'C' THEN C2 END) as 3_C2,
       MAX(CASE WHEN C1 = 'A' THEN C3 END) as 1_C3,
       MAX(CASE WHEN C1 = 'B' THEN C3 END) as 2_C3,
       MAX(CASE WHEN C1 = 'C' THEN C3 END) as 3_C3
  from table1
 where C1 in ('A', 'B', 'C')
   and C4 = 'X';