如何在输出Oracle SQL Developer时从事例中删除NULL

Question

Oracle SQL Developer noob此处。我试图根据仓库名称创建季度销售分析，并使用向下搜索查询概念将季度销售额输出为Q1、Q2等。

我不确定我所做的是否与"向下搜索"概念有任何关系，但这是我的众多尝试之一。我希望有一种方法可以删除空值输出，留下正确的数据。

希望随着空数据的删除，与特定仓库名称相关的所有输出也将全部输出到1行。留给我仓库名称(1(、Q1数据、Q2数据等

我目前正在使用两个表进行此查询，它们是仓库：Warehouse_id、Warehouse_name、quantity_seld时间周期：Date_id、Full_Date、Days、Month_short和year。

我的代码如下：

SELECT TO_CHAR(Full_date, 'Q') AS MY_QTR, 
Sum(Quantity_sold) AS HOW_MANY_SOLD_PER_QTR
FROM warehouse, Time_Period 
GROUP BY TO_CHAR(Full_date, 'Q')
ORDER BY 1;
Select warehouse_Name,
case 
when TO_CHAR(Full_date, 'Q') = 1
then Sum(Quantity_sold) 
End as Q1_2019,
case 
when TO_CHAR(Full_date, 'Q') = 2
then Sum(Quantity_sold)
End as Q2_2019,
case 
when TO_CHAR(Full_date, 'Q') = 3
then Sum(Quantity_sold)
End as Q3_2019,
case 
when TO_CHAR(Full_date, 'Q') = 4
then Sum(Quantity_sold)
End as Q4_2019
FROM warehouse w1, Time_Period t1
where Q1_2019 IS NOT NULL
GROUP BY warehouse_Name,TO_CHAR(Full_date, 'Q')
ORDER BY 1;

它为我提供了的输出

Waarehouse_Name   Q1   Q2   Q3   Q4
--------------- ---- ---- ---- ----
Henderson        990 Null Null Null    
Henderson       Null 1001 Null Null
Henderson       Null Null 1012 Null
Henderson       Null Null Null 1012

Answer 1

编辑：

如@mathguy 所述

他在GROUP by子句中使用了条件SUM(按季度条件(，也按季度分组。如果你从GROUP by中删除季度(你最终做到了(，那么输出中就不会再有null了。答案的要点与在大小写表达式中添加else 0有关。

所以在这里使用else是不必要的从GROUP BY中删除quarter即可。

Select warehouse_Name,  SUM( case when TO_CHAR(Full_date, 'Q') = 1 then Quantity_sold else 0 End) as Q1_2019,
SUM( case when TO_CHAR(Full_date, 'Q') = 2 then Quantity_sold else 0 End) as Q2_2019,
SUM( case when TO_CHAR(Full_date, 'Q') = 3 then Quantity_sold else 0 End) as Q3_2019,
SUM( case when TO_CHAR(Full_date, 'Q') = 4 then Quantity_sold else 0 End) as Q4_2019
FROM warehouse w1
where Full_date IS NOT NULL
GROUP BY warehouse_Name
ORDER BY 1;