如何使用JOIN和WHERE创建查询，或者如何让它们成为朋友

Question

我需要进行一个查询，其中有客户名称和每月订单的列。有些客户在几个月内没有订单，其中的字段必须为0。问题是，当我在一个查询*中使用WHERE和OUTER JOIN(无论是哪一个(时，必然会有零被WHERE截断。那么我该怎么解决呢？

表的描述被固定。

SELECT name
, ordering.id_client
, COUNT(order_date) 
FROM ordering 
RIGHT 
OUTER 
JOIN client 
ON client.id_client = ordering.id_client 
WHERE month(order_date) = 1 
GROUP 
BY name;

**Descripton**: (https://i.imgur.com/TrUGOLW.png)
**Example of my query** (there are 6 notes about clients at my db, showed only 4 of 6):
(https://i.imgur.com/ABP6pP0.png)
**MRE stuff**
Client: create table client(id_client int primary key auto_increment, name var char(50), passport_code int, addr varchar(70));
insert into client values(null, 'Penny Anderson', 6485, 'New Orlean');
Ordering: create table ordering(id_order int primary key auto_increment, id_client int, order_date date, foreign key(id_client) references client(id_client));
insert into ordering values(null, 1, date('2020-05-01'));

Answer 1

尝试从客户端的表开始的简单左联接

SELECT client.name
, client.id_client
, COUNT(order_date) 
FROM client 
LEFT JOIN  ordering  ON client.id_client = ordering.id_client 
AND  month(ordering.order_date) = 1 
GROUP  BY client.id_client;

如果联接的条件与左联接表相关，则将此条件添加到相关的ON子句中，而不是添加到其他情况下，此条件将作为内部联接工作