Postgresql中的子查询与预期数据不匹配

Question

当使用下面的查询时，我从数据库中获得了7条记录。

SELECT pickup_date::date, 
       SUM(CASE WHEN paid ='Yes' THEN price  ELSE 0  END) AS  TotalMoMoPaid 
from requests 
where order_status = 'Done' 
  and payment_mode = 'MoMo' 
  and pickup_date::date >= current_timestamp::date - INTERVAL '7 days' 
GROUP BY pickup_date::date,paid,order_status,price

​

​

当相同的查询用作子查询时，我得到了2条记录，这不是我所期望的，

SELECT pickup_date::date,
       sub.TotalMoMoPaid, 
       SUM(CASE WHEN order_status ='Done' THEN price  ELSE 0  END) AS "TotalCashSales" 
from (
  SELECT paid as subPaid,
         order_status as subStatus,
         price as subPrice, 
         SUM(CASE WHEN paid ='Yes' THEN price  ELSE 0  END) AS  TotalMoMoPaid 
  from requests 
  where order_status = 'Done' 
    and payment_mode = 'MoMo' 
    and pickup_date::date >= current_timestamp::date - INTERVAL '7 days' 
  GROUP BY pickup_date::date,subPaid,subStatus,subPrice
) AS sub, requests  
where order_status ='Done' 
  and payment_mode = 'Cash' 
  and pickup_date::date >= current_timestamp::date - INTERVAL '7 days'
GROUP BY sub.TotalMoMoPaid,subPaid,pickup_date::date 
ORDER BY sub.TotalMoMoPaid,pickup_date::date

​

​

Answer 1

这个查询应该可以工作，而不是使用子查询；

SELECT pickup_date::date,
       SUM(CASE WHEN payment_mode = 'MoMo' and paid = 'Yes' THEN price  ELSE 0  END) AS TotalMoMoPaid,
       SUM(CASE WHEN payment_mode = 'Cash' and paid = 'Yes' THEN price  ELSE 0  END) AS TotalCashSales
FROM requests 
WHERE order_status = 'Done' 
      and pickup_date::date >= current_timestamp::date - INTERVAL '7 days' 
GROUP BY pickup_date::date,paid,order_status,price