MySql 分组并包含 null

Question

我有这两张表:

Table members
| id | name    |
----------------
| 01 | John    |
| 02 | Michael |
| 03 | Richard |

和

Table payments
| id  | eur  | name    | date       |
-------------------------------------
| 238 | 9.95 | John    | 1323377751 |
| 233 | 9.95 | Michael | 1397864609 |
| 220 | 9.95 | Michael | 1397852739 |
| 215 | 9.95 | John    | 1397852719 |
|   2 | 9.95 | Richard | 1323377751 | // This payment was made more than 24h ago

以下是我的查询:

select id, m.name, sum(eur) as total 
from mambers as m
left join payments as p
on m.name = p.name
where p.date >= unix_timestamp(current_timestamp - interval 24 hour)
group by m.name

返回结果是:

| John    | 19.90 |
| Michael | 19.90 |  

但我还需要包括过去 24 天内没有付款的成员(member)，如下所示:

| John    | 19.90 |
| Michael | 19.90 |
| Richard | 00.00 |

Answer 1

尝试查询时出现两个错误。

1. 表名mambers不存在。

2. 列名 id 不明确。

试试这个:

select m.id, m.name, IFNULL(sum(eur),'00.00') as total 
from members as m
left join payments as p
on m.name = p.name and p.date >= unix_timestamp(current_timestamp - interval 24 hour)
group by m.name

结果:

ID  NAME      TOTAL
1   John      10
2   Michael   20
3   Richard   00.00

查看结果 SQL Fiddle .