如何显示每个月总利润，在oracle中没有记录时显示null

Question

我正在制作一份报告，显示2018年每个月的总利润，并在某些月份没有利润时显示为NIL

赚取的利润= 0.1 * Total_payment。利润是在服务完成时获得的，"Total_payment“列来自于预订，我必须加入预订和服务才能得到每个月的总利润，Booking_num是加入预订和服务的关键，Actual_end是服务的结束日期。

现在的问题是，1月、2月和8月都没有利润。这三个月的利润列中有没有显示为零的内容？

SELECT EXTRACT(MONTH FROM Actual_end) AS MONTH,SUM(Total_payment *0.1) AS PROFIT
FROM SERVICE,BOOKING
WHERE SERVICE.Booking_num = BOOKING.Booking_num
AND EXTRACT(YEAR FROM Actual_end) = 2018
GROUP BY EXTRACT(MONTH FROM Actual_end);

这是9个月显示利润的代码，没有1月、2月和8月

MONTH  PROFIT
3      88.4
4      146.1
5      112.6
6      108.3
7      102.6
9      130.3
10     72.6
12     124.9

我希望输出结果是

MONTH    PROFIT
1        NIL
2        NIL
3        88.4
4        146.1 
5        112.6
6        108.3
7        102.6
8        NIL
9        130.3
10       72.6
11       124.9
12       25.2

怎么修改，我也试过了

WITH CALENDAR AS(
SELECT TO_CHAR(add_months(date '2018-01-01',ROWNUM -1),'MM') AS MONTH
FROM DUAL
CONNECT BY LEVEL <=12)
SELECT CALENDER.MONTH, NVL(SUM(Total_payment*0.1),null) AS PROFIT
FROM BOOKING,SERVICE,CALENDER
WHERE BOOKING.Booking_num = SERVICE.Booking_num
AND CALENDER.MONTH = EXTRACT(MONTH FROM Actual_end(+))
AND EXTRACT(MONTH FROM Actual_end) = 2018
GROUP BY CALENDER.MONTH

输出:未选择任何行

Answer 1

在这种情况下，您需要所有月份的列表：

with months as (
      select 1 as month from dual union all
      select 2 as month from dual union all
      select 3 as month from dual union all
      select 4 as month from dual union all
      select 5 as month from dual union all
      select 6 as month from dual union all
      select 7 as month from dual union all
      select 8 as month from dual union all
      select 9 as month from dual union all
      select 10 as month from dual union all
      select 11 as month from dual union all
      select 12 as month from dual
     )
select m.month, sum(s.total_payment * 0.1) as profit
from months m left join
     booking b
     on extract(month from b.actual_end) = m.month and
        b.actual_end >= date '2018-01-01' and
        b.actual_end < date '2019-01-01' left join
     service s join
     on s.booking_num = b.booking_num 
group by m.month;

注意：

• 这是猜测，actual_end在booking中，total_payment在service中。如果此猜测不正确，查询将略有不同。
• 从不在FROM子句中使用逗号。
• 此查询应使用LEFT JOIN。第一个表的所有日期行都在on子句中，而不是在where子句中。
• 请注意
• 常量的使用。这样的比较通常使引擎更容易优化查询(通常通过使用索引)。