mysql - 如何使用左连接查询以获得结果

Question

我在 mysql 上处理两个表，我试图从左表中获取所有行以及第二个表中与第一个表匹配的列。

业务

id | business_name
-------------------
1   |abc    
2   |def    
3   |ghi    
4   |jkl    
5   |mno

订单

business_id_fk  | order_status  | date 
----------------------------------------    
2               |  PCK         |  30-03-2017    
3               |  DEL         |  30-03-2017    
2               |  DEL         |  30-03-2017    
2               |  PCK         |  30-03-2017    
4               |  PCK         |  28-03-2017    
3               |  PCK         |  29-03-2017    
4               |  DEL         |  30-03-2017

我希望业务表中的所有行以及订单表中 2017 年 3 月 30 日每个业务的每个订单状态的计数按总数排序。结果集是:

id | business_name | total(order_status) | count(PCK) | count(DEL) 
----------------------------------------------------
2  | def           | 3       | 2      |  1   
3  | ghi           | 1       | 0      |  1    
4  | jkl           | 1       | 0      |  1    
1  | abc           | 0       | 0      |  0    
5  | mno           | 0       | 0      |  0

请帮助我查询得到上述结果。

Answer 1

您可以在连接上使用条件聚合:

select b.id,
    b.business_name,
    count(o.order_status) as total_count,
    coalesce(sum(o.order_status = 'PCK'), 0) as count_PCK,
    coalesce(sum(o.order_status = 'DEL'), 0) as count_DEL
from business b
left join orders o on b.id = o.business_id_fk
    and o.date = '2017-03-30'
group by b.id,
    b.business_name;

我假定订单表中日期列的数据类型为日期(或至少格式为 YYYY-MM-DD 的字符串)。

Demo