mysql - 展平两个表并使用group by

我有两个这样的交易表：

TableA

 X  R  S
 71 1 10
 71 2 20
 71 3 30
 72 1 40
 72 2 50
 72 3 60

TableB


 X  P Q
 71 1 110
 71 2 120
 71 3 130
 73 1 140
 73 2 150
 73 3 160

我想展平表并执行查询，结果如下：

  X  S1  S2  S3   Q1  Q2  Q3

  71 10  20  30  110 120 130
  72 40  50  60   0    0   0
  73  0   0   0  140 150 160

其中第二列等于下标时，S和Q中的下标表示第三列的值。例如，当第二列等于1时，S1表示S列值；当第二列等于2时，Q2表示Q值。

我不知道什么是最好的方法。也许我可以使用case语句，也可以是子查询。但老实说，我对此没有具体想法。

最佳答案

您可能希望通过联接和两个聚合来实现：

select coalesce(a.x, b.x) as x, a.s1, a.s2, a.s3, b.q1, b.q2, b.q3
from (select x,
             max(case when r = 1 then s end) as s1,
             max(case when r = 2 then s end) as s2,
             max(case when r = 3 then s end) as s3
      from tableA a
      group by x
     ) a full outer join
     (select x,
             max(case when p = 1 then q end) as q1,
             max(case when p = 2 then q end) as q2,
             max(case when p = 3 then q end) as q3
      from tableb b
      group by x
     ) b
     on a.x = b.x;

编辑：

如果要避免嵌套子查询，可以尝试：

select coalesce(a.x, b.x) as x,
       max(case when r = 1 then s end) as s1,
       max(case when r = 2 then s end) as s2,
       max(case when r = 3 then s end) as s3
       max(case when p = 1 then q end) as q1,
       max(case when p = 2 then q end) as q2,
       max(case when p = 3 then q end) as q3
from TableA a full outer join
     TableB b
     on a.x = b.x and a.r = b.p
group by coalesce(a.x, b.x);

它甚至可能更有效。