Oracle 查询以根据条件连接两个表并分组依据

Question

我有一个表格在下面的格式，

表1

tbody> <<tr>

银行	类别	Month_Year	Loan_Type	未偿付
如果	R1	jan 21	家	10
如果	R1	jan 21	土地	50
如果td>	FEB-21	家	30
如果td>	三月二十一	汽车	40

主要在Category和Month_Year列后的表中选择一个CROSS JOIN，对于不匹配的值在主查询中添加Outstanding列作为零，否则返回其值如

SELECT t2.Bank, t2.Category, t2.Month_Year, t2.Loan_Type, 
NVL(t1.Outstanding,0) AS Outstanding
FROM (SELECT *
FROM (SELECT DISTINCT Category, Month_Year FROM table1) 
CROSS JOIN table2) t2
LEFT JOIN table1 t1
ON t2.Category = t1.Category
AND t2.Month_Year = t1.Month_Year
AND t2.Loan_Type = t1.Loan_Type
ORDER BY t2.Category, t2.Month_Year, t1.Outstanding NULLS LAST

创建一个表1所需和左连接的所有项的列表。例如

select items.Bank, items.Category, items.Month_Year, items.Loan_Type, coalesce(t1.Outstanding, 0) Outstanding
from (
select t2.Bank, t2.Loan_Type, my.Month_Year,  cat.Category
from (select distinct Month_Year
from Table1) my
cross join (select distinct Category
from Table1) cat
cross join Table2 t2
) items
left join Table1 t1 on items.Bank = t1.Bank and items.Loan_Type = t1.Loan_Type and items.Month_Year = t1.Month_Year and items.Category = t1.Category;

如果存在表Categories，则替换它而不是查询中的派生类别。您可能还希望从参数生成一组Month_Year或使用日历表。