mysql - 与其他年份相比，如果一年内没有发生销售，则缺少行

下面给出了表定义和数据:

CREATE TABLE bike
(
  id INTEGER,
  name VARCHAR(50),
  price INTEGER
);

CREATE TABLE country
(
  id INTEGER,
  country VARCHAR(50)
);

create table bike_sales
(
  bike_id int,
  country_id int,
  quantity int,
  sales_date DATE
);

INSERT INTO bike VALUES(1,'XYZ',50000);
INSERT INTO bike VALUES(2,'ABC',70000);
INSERT INTO bike VALUES(3,'PQR',70000);

INSERT INTO country VALUES(1,'US');
INSERT INTO country VALUES(2,'Canada');
INSERT INTO country VALUES(3,'UK');

INSERT INTO bike_sales VALUES(1, 1, 5, '2018-01-01');
INSERT INTO bike_sales VALUES(1, 2, 10, '2018-02-01');
INSERT INTO bike_sales VALUES(1, 3, 7, '2018-03-01');
INSERT INTO bike_sales VALUES(2, 1, 9, '2018-04-01');
INSERT INTO bike_sales VALUES(2, 2, 8, '2018-05-01');
INSERT INTO bike_sales VALUES(2, 3, 4, '2018-06-01');
INSERT INTO bike_sales VALUES(3, 3, 4, '2019-06-01');

当我运行此查询时:

SELECT c.country, b.name, (bs.quantity* b.price) as revenue
FROM bike_sales bs
  LEFT JOIN country c
    ON c.id = bs.country_id
  LEFT JOIN bike b
    ON b.id = bs.bike_id
WHERE year(bs.sales_date) = '2018'
order by c.country, b.name

我得到这个输出

+---------+------+---------+
| country | name | revenue |
+---------+------+---------+
| Canada  | ABC  |  560000 |
| Canada  | XYZ  |  500000 |
| UK      | ABC  |  280000 |
| UK      | PQR  |  280000 |
| UK      | XYZ  |  350000 |
| US      | ABC  |  630000 |
| US      | XYZ  |  250000
+---------+------+---------+

我正在计算 2018 年的全国销量。我还希望我的结果显示 2019 年售出的自行车，即使它们在 2018 年没有售出。例如:2018 年在“英国”中没有“PQR”的销量，尽管它于 2019 年在英国出售，因此由于 2018 年没有销售，因此我也需要输出中的那一行，收入为 0。我如何得到这个缺失的行？

| UK      | ABC  |  0 |

最佳答案

从现有查询开始，您可以过滤 2018 年和 2019 年，并在计算收入时使用 case 表达式:

select
    c.country,
    b.name,
    case when year(bs.sales_date) = 2018 THEN bs.quantity * b.price else 0 end as revenue
from
    bike_sales bs
    left join country c on c.id = bs.country_id
    left join bike b on b.id = bs.bike_id
where year(bs.sales_date) in (2018, 2019)
order by c.country, b.name

Demo on DB Fiddle :

| country | name | revenue |
| ------- | ---- | ------- |
| Canada  | ABC  | 560000  |
| Canada  | XYZ  | 500000  |
| UK      | ABC  | 280000  |
| UK      | PQR  | 0       |
| UK      | XYZ  | 350000  |
| US      | ABC  | 630000  |
| US      | XYZ  | 250000  |

旁注:mysql 函数 year() 返回一个数字(不是字符串)。

关于mysql - 与其他年份相比，如果一年内没有发生销售，则缺少行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58441396/