Блог

Моя база данных :

   TB_DW_VAB_FLIGHT :  ID_TEC_FLIGHT
  TB_DW_VAB_SALES  :  QUANTITY, TRANSACTION_NUMBER, UNIT_SALES_PRICE
  

Я хочу иметь таблицу с 4 столбцами в качестве результата: CA, QTE, NB_TRANSACTION и NB_VOLS в том же месяце. (N-1)

Я попробовал SQL-запрос, подобный этому :

     SELECT 
  sum(QUANTITY*UNIT_SALES_PRICE) as CA,
  sum(QUANTITY) as QTE,
  count(distinct TRANSACTION_NUMBER) as NB_TRANSACTION,
  count(distinct ID_TEC_FLIGHT) as NB_VOLS
FROM TB_DW_VAB_SALES, TB_DW_VAB_FLIGHT
where to_char(TB_DW_VAB_SALES.FLIGHT_DATE,'MM')=to_char(current_date,'MM')-1 and to_char(TB_DW_VAB_SALES.FLIGHT_DATE,'YYYY')=to_char(current_date,'YYYY') and SALES_TYPE='SALES'
and to_char(TB_DW_VAB_FLIGHT.FLIGHT_DATE,'MM')=to_char(current_date,'MM')-1 and to_char(TB_DW_VAB_FLIGHT.FLIGHT_DATE,'YYYY')=to_char(current_date,'YYYY');
  

Но Oracle не может дать мне ответ.

Большое спасибо за любую помощь.

Попробуйте

 with CTE1 as 
(
select to_char(FLIGHT_DATE, 'MM-YYYY') as PERIOD,
       sum(QUANTITY*UNIT_SALES_PRICE) as CA,
       sum(QUANTITY) as QTE,
       count(distinct TRANSACTION_NUMBER) as NB_TRANSACTION
from TB_DW_VAB_SALES
where SALES_TYPE = 'SALES'
group by to_char(FLIGHT_DATE, 'MM-YYYY')
)
, CTE2 as 
(
select count(distinct ID_TEC_FLIGHT) as NB_VOLS, 
       to_char(FLIGHT_DATE, 'MM-YYYY') as PERIOD
from TB_DW_VAB_FLIGHT
group by to_char(FLIGHT_DATE, 'MM-YYYY')
)
select CTE1.CA, 
       CTE1.QTE, 
       CTE1.NB_TRANSACTION, 
       CTE2.NB_VOLS
from CTE1
inner join CTE2 on CTE1.PERIOD = CTE2.PERIOD
where CTE1.PERIOD = to_char(add_Months(sysdate,-1),'MM-YYYY')
  

или если CTE недоступны в вашем программном обеспечении:

 select CTE1.CA, 
       CTE1.QTE, 
       CTE1.NB_TRANSACTION, 
       CTE2.NB_VOLS
from 
(
select to_char(FLIGHT_DATE, 'MM-YYYY') as PERIOD,
       sum(QUANTITY*UNIT_SALES_PRICE) as CA,
       sum(QUANTITY) as QTE,
       count(distinct TRANSACTION_NUMBER) as NB_TRANSACTION
from TB_DW_VAB_SALES
where SALES_TYPE = 'SALES'
group by to_char(FLIGHT_DATE, 'MM-YYYY')
) CTE1
inner join 
(
select count(distinct ID_TEC_FLIGHT) as NB_VOLS, 
       to_char(FLIGHT_DATE, 'MM-YYYY') as PERIOD
from TB_DW_VAB_FLIGHT
group by to_char(FLIGHT_DATE, 'MM-YYYY')
) CTE2 
    on CTE1.PERIOD = CTE2.PERIOD
where CTE1.PERIOD = to_char(add_Months(sysdate,-1),'MM-YYYY')