MYSQL:将值列表加入表中 [英] MYSQL: Join list of values into a table

问题描述

我有预定义值列表Cash、Bank、Card、Cheque 称为支付模式 (他们没有数据库表).

I have list of predefined values Cash, Bank, Card, Cheque called payment modes (no db table for them).

每个payments 都会有它的模式.

Each payments will have it's mode.

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

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

The below query will not show mode that are not in the payments table. If for example no payment is made through cheque, then it will not be in the result.

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

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

I also found out about Table Value Constructor but I'm not sure if its supported in MySql as I'm having syntax error.

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 表中?我需要这样的结果:

Is there a way to have a query that gets all the mode regardless if they are not present in the payments table? I need a result like:

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

推荐答案

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

In MySQL, you can construct the table using 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 键需要来自 first 名称，而不是 second(即 m.modecode> 而不是 p.mode).
• 如果你想要 0 而不是 NULL，那么使用 coalesce(sum(p.amount), 0).
• 您可能需要考虑一个包含众数值的参考表.

• I changed name to mode so the column with the same information has the same name.
• The group by key needs to be from the first name, not the second (that is m.mode instead of p.mode).
• If you want 0 instead of NULL, then use coalesce(sum(p.amount), 0).
• You might want to consider a reference table that contains the mode values.

这篇关于MYSQL:将值列表加入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！