MySQL 使用 join 对多个表中的行进行计数

Question

我有 3 个 MySQL 表:

**locations**
id      location_name
1       London
2       New York
3       Washington

**bookings**
id      voucher_code    location_id
1       DISC100         1
2       NULL            NULL
3       DISC200         1
4       DISC500         2
5       DISC700         1

**vouchers**
id      voucher_code    net_value
1       DISC100         155.00
2       DISC200         135.00
2       DISC500         155.00
2       DISC700         155.00

我正在尝试显示每个位置的代金券净值计数。例如，您可以看到“伦敦”位置有 3 张已在 bookings 表中兑换的代金券，其中 2 张是 155.00 值(value)的代金券，一张是 135.00。我需要编写一个 MYSQL 查询来从数据库中提取这些计数。

因此，以上述表格为例，我的 SQL 查询结果应如下:

Location            netValue155     netVaue135
London              2               1
New York            1               0
Washington          0               0

下面是我到目前为止写的:

select 
    l.location_name,
    '' as date_from, 
    '' as date_to,
    v.netValue155
from locations l 
left join (select voucher_code, count(distinct id) as netValue155 from vouchers where net_value='155.00' group by id) as v

Answer 1

select a.location_name, sum(netvalue155), sum(netvalue135) from
(select l.id ,l.location_name,
case when v.net_value = 155 then 1 else 0 end as netvalue155,
case when v.net_value = 135 then 1 else 0 end as netvalue135
from locations l left join bookings b
on l.id = b.location_id
left join vouchers v
on  b.voucher_code = v.voucher_code) a
right join locations l on l.id = a.id
group by a.location_name

[sql fiddle ] http://sqlfiddle.com/#!9/daefd/19