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   +
++++++++++++++++++++

- Jaime Sangcap

---

请添加一些示例表格数据和预期结果。（所有内容均为格式化文本，不包括图像。） - jarlh

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)。
• 你可能希望考虑一个包含模式值的参考表。

- Gordon Linoff

---

我认为m(mode)不是有效的语法？ - P.Salmon

@Gordon 谢谢！我尝试了你的答案，但是出现了语法错误。也许在MySql中不支持？我想我只需要添加模式表。 - Jaime Sangcap

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）作为所选列。

- Andrei Lupuleasa

Answer 3

如果VALUES方法不起作用，检查一下你的MySQL版本是值得的。它只在MySQL 8.0.19中引入，因此任何使用早期版本的人都需要使用UNION ALL方法。

- Geoff Kendall