mysql 我试图让一个客户谁已取消所有订单，我是无法做到这一点

wz3gfoph 于 2023-06-28 发布在 Mysql

关注(0)|答案(1)|浏览(102)

SELECT
   oc.customer_id
  ,concat(oc.customer_fname,' ',oc.customer_lname) as Full_name
  ,oc.customer_email
  ,oc.customer_phone
  ,a.country
FROM online_customer oc
JOIN address a ON oc.address_id = a.address_id
WHERE oc.customer_id = (
  SELECT
    oh.customer_id
 FROM order_header oh
 WHERE oh.order_status = 'Cancelled'
)
GROUP BY oc.customer_id, concat(oc.customer_fname,' ', oc.customer_lname), oc.customer_email, oc.customer_phone, a.country;

order_header表中有一个名为Order_status的列，其值为“已发货”、“正在处理”和“已取消”。现在我需要一个在order_status中所有订单都为“Cancelled”的customer_id。我无法理解这个逻辑

mysql

来源：https://stackoverflow.com/questions/76564338/i-am-trying-to-get-a-customer-who-has-cancelled-all-orders-i-am-not-able-to-do

1条答案

按热度按时间

kwvwclae1#

您可以通过按customer_id对订单表进行分组，然后检查它只有一个不同的order_status，并且该状态（使用max/min）为“Canceled”来实现此逻辑

select oc.customer_id,concat(oc.customer_fname,' ',oc.customer_lname) as Full_name,oc.customer_email,oc.customer_phone,
a.country
from online_customer oc join address a on oc.address_id = a.address_id
where oc.customer_id=(select oh.customer_id
from order_header oh 
group by customer_id 
having count(distinct order_status) = 1 and max(order_status) = 'Cancelled')
group by oc.customer_id,concat(oc.customer_fname,' ',oc.customer_lname),oc.customer_email,oc.customer_phone,a.country;

赞(0）回复(0）举报 2023-06-28

我来回答

mysql 我试图让一个客户谁已取消所有订单，我是无法做到这一点

1条答案

相关问题

热门标签

最新问答