MYSQL:将值列表联接到表中

Question

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

每个payments都将具有其模式。

+-----------+
+ 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) 。
• 您可能需要考虑包含模式值的引用表。

Answer 2

select 
  m.mode, 
  SUM(p.amount) 
FROM 
  (
    SELECT 
      m.Cash as mode 
    FROM 
      (
        VALUES 
          ('cash'), 
          ('card'), 
          ('cheque'), 
          ('bank')
      ) as m
  ) as m 
  left join payments p on m.mode = p.mode 
group by 
  m.mode

您需要两个别名，并且必须在嵌套选择中将数组的第一个值(Cash(用作选定列。

Answer 3

如果 VALUES 方法不起作用，则值得检查您的 MySQL 版本。它只是从MySQL 8.0.19引入的，所以任何早期问题的人都需要使用UNION ALL方法。