MYSQL:将值列表连接到表中

Question

我有预定义值列表 Cash、Bank、Card、Cheque 称为 payment modes (他们没有数据库表)。

每个支付都会有它的模式。

+-----------+
+ Payments  +
+-----------+
+ id        +
+ amount    +
+ date      +
+ mode      +
+-----------+

下面的查询不会显示 payments 表中没有的 mode。例如，如果没有通过 cheque 进行支付，那么它就不会出现在结果中。

select p.mode, SUM(p.amount) 'total'
from payments p

我还发现了 Table Value Constructor 但我不确定它是否在 MySql 中受支持，因为我有语法错误。

select p.mode, SUM(p.amount)
from (
    VALUES
      ('Cash'),
      ('Card'),
      ('Cheque'),
      ('Bank')
 ) as m(name) 
 left join payments p on m.name = p.mode
group by p.mode

有没有一种方法可以获取所有模式的查询，而不管它们是否不存在于 payments 表中？我需要这样的结果:

++++++++++++++++++++
+ mode     | total +
++++++++++++++++++++
+ cash     | 100   +
+ cheque   | 0     +
+ bank     | 0     +
+ card     | 300   +
++++++++++++++++++++

Answer 1

在 MySQL 中，您可以使用 union all 构造表:

select m.mode, SUM(p.amount)
from (select 'Cash' as mode union all
      select 'Card' union all
      select 'Cheque' union all
      select 'Bank'
     ) m left join
     payments p
     on m.mode = p.mode
group by m.mode;

注意事项:

• 我将 name 更改为 mode 因此具有相同信息的列具有相同的名称。
• group by 键需要来自第一个 名称，而不是第二个(即m.mode 而不是 p.mode)。
• 如果您想要 0 而不是 NULL，则使用 coalesce(sum(p.amount), 0)。
• 您可能需要考虑一个包含模式值的引用表。