mysql - 带分组的 SQL 子查询

Question

SELECT customer_email, count(*) AS Order_Count,

MAX(created_at) as Last_Order_Date, 
SUM(base_total_paid) AS Total_Lifetime_Sales, 
SUM(base_total_offline_refunded+base_total_online_refunded) AS Refund_Total, 

FROM mage_sales_order AS o

WHERE o.created_at > “2018-01-01” 

AND

value NOT IN (Select customer_email 
FROM mage_sales_order
WHERE WHERE o.created_at < “2018-10-01”)

尝试删除上周购买的订单，但卡在“WHERE AND”上，并且不确定!感谢您的任何帮助!

Answer 1

我认为您想要删除订购 < '2018-01-01' 的电子邮件 ID，以便它位于查询下方

SELECT customer_email, count(*) AS Order_Count,     
MAX(created_at) as Last_Order_Date, 
SUM(base_total_paid) AS Total_Lifetime_Sales, 
SUM(base_total_offline_refunded+base_total_online_refunded) AS Refund_Total     
FROM mage_sales_order AS o     
WHERE o.created_at > '2018-01-01' AND    
customer_email NOT IN (Select customer_email 
FROM mage_sales_order
WHERE  o.created_at < '2018-10-01'
)
group by customer_email