显示每个月并计数或0(如果为空)

Question

我使用下面的DDL显示2018年和2019年几个月的销售数据。我的问题是，很明显，我们将没有数据为2019年9月至12月，但我需要返回，并显示一个0的金额。

如何编辑此查询，以便将18和19的所有月份都返回1-12个月？

CREATE TABLE PrevYear (
  [EmployeeNumber] char(8) NOT NULL,
  [SaleAmount] int DEFAULT NULL,
  [SaleDate] date NOT NULL,
  [EmployeeName] char(17) NOT NULL
); 

CREATE TABLE CurrentYear (
  [EmployeeNumber] char(8) NOT NULL,
  [SaleAmount] int DEFAULT NULL,
  [SaleDate] date NOT NULL,
  [EmployeeName] char(17) NOT NULL
); 

INSERT INTO CurrentYear
VALUES ('ea12', '100', '2019-01-10', 'Sam Smith');

INSERT INTO CurrentYear
VALUES ('ea12', '199', '2019-01-13', 'Sam Smith');

INSERT INTO CurrentYear
VALUES ('ea12', '100', '2019-03-01', 'Sam Smith');

INSERT INTO CurrentYear
VALUES ('ls22', '100', '2019-05-01', 'Sam Smith');


INSERT INTO PrevYear
VALUES ('ea12', '100', '2018-01-10', 'Sam Smith');

INSERT INTO PrevYear
VALUES ('ea12', '199', '2018-01-13', 'Sam Smith');

INSERT INTO PrevYear
VALUES ('ea12', '100', '2018-03-01', 'Sam Smith');

INSERT INTO PrevYear
VALUES ('ls22', '100', '2018-05-01', 'Sam Smith');

我想从查询中得到的输出是：

1月18日1美元

1月19日1美元

2月18日$1

2月19日1美元

3月18日$1

3月19日$1

4月18日$1

4月19日$1

五月十八日$1

五月十九日$1

六月十八日1元

六月十九日$1

7月18日1美元

7月19日1美元

8月18日$1

8月19日1美元

9月18日1美元

9月19日0美元

10月18日$1

10月19日0美元

11月18日1美元

11月19日0美元

12月18日$1

12月19日0美元

这是我的查询

SELECT Month, Sum(ia) AS SaleAmount 
FROM   (SELECT Date_format(saledate, '%m-%Y') AS Month, 
           employeename, 
           Sum(SaleAmount)                AS IA 
    FROM   CurrentYear 
    WHERE  employeename = 'Sam Smith' 
    GROUP  BY Date_format(saledate, '%m-%Y'), 
              employeename 
    UNION ALL 
    SELECT Date_format(saledate, '%m-%Y') AS Month, 
           employeename, 
           Sum(SaleAmount)                AS IA 
    FROM   PrevYear
    WHERE  employeename = 'Sam Smith' 
    GROUP  BY Date_format(saledate, '%m-%Y'), 
              employeename) previousQuery 
GROUP  BY month 
ORDER  BY month

Answer 1

您可以简单地添加一个包含12条记录(每个月一条)的月查找表。

结构将是: id，描述。

在现有表中，您可以按id引用月份:更少的空间，更多的连接性能。

在使用左/右联接的查询中，您将始终有缺少的月份。

PS:只需要一个表来管理它，并在需要的地方添加一个简单的where条件(或按条件分组)，以便进行多年的管理。