SQL如何在以下情况下联接两个表以获取记录？

Tiger 发表于 Dev

老虎

付款方式

Year     Month       Division       Department   Payments_received_Count
------------------------------------------------------------------------
2016       1           Electric      dep1         2
2016       1           Electric      dep2         3
2015       1           Electric      dep1         1

师

Division             Department   
--------------------------------
Electric             Dep1                      
Electric             Dep2                 
Electric             Dep3

如何联接表以获得以下结果？

Year     Month       Division       Department   Payments_received_Count
------------------------------------------------------------------------
2016       1           Electric      dep1         2
2016       1           Electric      dep2         3
2016       1           Electric      dep3         0
2015       1           Electric      dep1         1
2015       1           Electric      dep2         0
2015       1           Electric      dep3         0

因此，对于每一年和每个月，我都必须将付款表与部门表结合起来。请提出一种方法。

我知道CASE WHEN在这种情况下会有所帮助，但我一直找不到正确的方法。

CASE Payments_received_Count
     WHEN null THEN 0
     ELSE Payments_received_Count

谢谢

戈登·利诺夫

您需要部门/部门和年/月的组合。首先，使用生成行cross join。然后使用left join来获取值：

select ym.year, ym.month, d.division, d.department,
       coalesce(Payments_received_Count, 0) as Payments_received_Count
from divisions d cross join
     (select distinct year, month from payments) ym left join
     payments p
     on d.division = p.division and d.department = p.department and
        ym.year = p.year and ym.month = p.month
order by year desc, month desc, division, department;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。