如何仅显示两个选项的在线订单 - 直接和在线

Question

我需要通过删除购买的产品(order_mode ='在线(仅在线购买的产品(直接或直接购买的产品(。

我尝试了案例，但该条款不起作用

select p.product_name,
sum(o.quantity) as total_quantity,
case
when extract(month from p.warranty_period) = 0
    and extract(year from p.warranty_period) = 0 
        then 'No Warranty'
when extract(month from p.warranty_period) = 0
    then extract(year from p.warranty_period) || ' years'
when extract(year from p.warranty_period) = 0
    then extract(month from p.warranty_period) || ' months'
else extract(year from p.warranty_period) || ' years and ' ||
    extract(month from p.warranty_period) || ' months' 
 end WARRANTY, oe.order_mode from PRODUCT_INFORMATION p
join order_items o on p.product_id = o.product_id
join orders oe on o.order_id = oe.order_id
group by p.product_name, p.warranty_period, oe.order_mode
having sum(o.QUANTITY) > 200
order by p.product_name;

我希望仅在线购买订单_mode列。

Answer 1

没有任何产品满足您的需求量>200。我将限制设置为40。

select p.product_name,
sum(o.quantity) as total_quantity,
case
when extract(month from p.warranty_period) = 0
    and extract(year from p.warranty_period) = 0 
        then 'No Warranty'
when extract(month from p.warranty_period) = 0
    then extract(year from p.warranty_period) || ' years'
when extract(year from p.warranty_period) = 0
    then extract(month from p.warranty_period) || ' months'
else extract(year from p.warranty_period) || ' years and ' ||
    extract(month from p.warranty_period) || ' months' 
 end WARRANTY, 'online' order_mode from PRODUCT_INFORMATION p
join (
  select o.product_id, sum(o.quantity) quantity
  from order_items o
  join orders oe on o.order_id = oe.order_id
  group by o.product_id
  having sum(case oe.order_mode when 'direct' then 1 else 0 end) = 0
  and sum(o.quantity) > 40
) o on p.product_id = o.product_id
group by p.product_name, p.warranty_period
order by p.product_name;
PRODUCT_NAME           TOTAL_QUANTITY   WARRANTY               ORDER_MODE   
Chemicals - SW                     68   5 years                online        
DIMM - 32MB                        47   6 months               online        
PS 220V /HS/FR                     45   9 months               online        
Pens - 10/FP                       42   No Warranty            online        
Plastic Stock - B/HD               64   No Warranty            online        
TD 7GB/8                           59   1 years and 6 months   online