mysql之没有记录时如何显示结果为零

我有下面的 3 个表格，其中包含以下数据

create table model ( 
id int comment 'The model id. Primary Key', 
name varchar(50) comment 'Models name', 
price int comment 'The price of the model', 
primary key (id) 
); 
 
create table country ( 
id int comment 'The country id. Primary Key', 
name varchar(50) comment 'Countrys name', 
primary key (id) 
); 
 
create table sales ( 
model_id int comment 'Models id', 
country_id int comment 'Countrys id', 
quantity int comment 'Number of units sold', 
sales_date date comment 'Date of the sale' 
); 
 
INSERT INTO COUNTRY VALUES 
(1, 'Brazil'), 
(2, 'Italy'), 
(3, 'China'); 
 
INSERT INTO model VALUES 
(1, 'ZTX100', 100), 
(2, 'AHT567', 200), 
(3, 'JPF090', 300); 
 
INSERT INTO sales VALUES 
(1, '1', 500, '2017-01-10'), 
(2, '2', 600, '2017-05-11'), 
(3, '3', 700, '2017-07-21'), 
(1, '3', 200, '2019-08-14'); 

我需要编写一个查询来计算所有模型的国家/地区销售额以及 2017 年产生的收入。结果应采用以下格式:country_name |模型名称 |收入

我正在尝试下面的查询。我需要将没有任何销售额的国家/型号也带入 0 收入，但我的查询只返回有任何销售额的国家/型号。

SELECT c.name as country_name, 
       m.name as model_name,  
       IFNULL(SUM(s.quantity * m.price),0) as revenue  
    FROM 
    sales s 
     right join model m on s.model_id = m.id 
     right join country c on s.country_id = c.id 
where date(s.sales_date) between '2017-01-01' and '2017-12-31'     
group by c.id, m.id 
order by c.id, m.id; 

查询结果

Brazil  ZTX100  50000 
Italy   AHT567  120000 
China   JPF090  210000 

我真正需要的(顺序不重要)

Brazil  ZTX100  50000 
Brazil  AHT567  0 
Brazil  JPF090  0 
 
Italy   ZTX100  0 
Italy   AHT567  120000 
Italy   JPF090  0 
 
China   ZTX100  0 
China   AHT567  0 
China   JPF090  210000 

有一个简单的方法吗？

请您参考如下方法:

您可以交叉联接国家和模型表以生成所有可能的组合，然后通过左联接引入销售额:

SELECT  
    c.name as country_name, 
    m.name as model_name,  
    IFNULL(SUM(s.quantity * m.price),0) as revenue  
FROM  
    country c 
    cross join model m 
    left join sales s 
        on s.model_id = m.id 
        and s.country_id = c.id 
        and s.sales_date >= '2017-01-01'  
        and s.sales_date <  '2018-01-01'     
group by c.id, m.id, c.name, m.name 
order by c.id, m.id; 

请注意，我在查询中做了以下更改:

• 使用半开区间进行日期比较；这会更有效率，因为不需要在 sales_date

上应用日期函数

• 将所有非聚合列添加到 group by 子句，使查询符合 MySQL 模式 ONLY_FULL_GROUP_BY，从 MySQ 5.7 开始默认启用该模式