填补缺失的月份

Question

我有两个表，我需要通过查看其他表来填充缺失的值。

​

​

数据表

​

​

输出：

​

​

代码：-我确实交叉连接，但不确定如何用0值填充它。

SELECT a.* 
  FROM PRODUCTS a 
  JOIN "DATE" b
    ON a.YEAR=b.YEAR

Answer 1

我想你想要的是：

SELECT pc.*, d.*,
       COALESCE(p.revenue, 0) as revenue,
       . . . 
FROM DATE d CROSS JOIN
     (SELECT DISTINCT product_group, customer FROM PRODUCTS p) pc LEFT JOIN
     PRODUCTS p
     ON p.YEAR = d.YEAR AND p.MONTH = d.MONTH AND
        p.product_group = pc.product_group AND p.customer = pc.customer;