时间戳

首页 > 解决方案 > 没有记录时如何将结果显示为零

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

有一种简单的方法可以做到这一点吗？

标签： mysqlsql

解决方案

您可以cross join使用国家和模型表生成所有可能的组合，然后使用以下命令引入销售额left join：

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 开始默认启用

推荐阅读