mysql - SQL：我们可以选择一列中的内容作为新表中的新列吗？

Question

这是一个例子： 输入：

company    amount     vendor    ID(ai)
  A1        100         B1        1
  A2        150         B1        2
  A3        200         B2        3
  A1        100         B3        4

输出：

vendor   A1      A2      A3     Total
  B1     100     150     NULL   250
  B2     NULL    NULL    200    200
  B3     100     NULL    NULL   100

标签： mysqlsql

Answer 1

看起来你想要条件聚合：

select vendor,
       sum(case when company = 'A1' then amount end) as a1,
       sum(case when company = 'A2' then amount end) as a2,
       sum(case when company = 'A3' then amount end) as a3,
       sum(amount) as total
from t
group by vendor;