我正在尝试获取一个取消了所有订单的客户，但我无法做到。

huangapple
go评论146阅读模式
英文:

I am trying to get a customer who has cancelled all orders , I am not able to do so

问题

Here is the translated SQL query:

  1. SELECT
  2.    oc.customer_id
  3.   ,concat(oc.customer_fname,' ',oc.customer_lname) as Full_name
  4.   ,oc.customer_email
  5.   ,oc.customer_phone
  6.   ,a.country
  7. FROM online_customer oc
  8. JOIN address a ON oc.address_id = a.address_id
  9. WHERE oc.customer_id = (
  10.   SELECT
  11.     oh.customer_id
  12.  FROM order_header oh
  13.  WHERE oh.order_status = 'Cancelled'
  14. )
  15. GROUP BY oc.customer_id, concat(oc.customer_fname,' ', oc.customer_lname), oc.customer_email, oc.customer_phone, a.country;

The SQL query retrieves customer information for customers who have all their orders with an "Order_status" of "Cancelled" in the "order_header" table.

英文: 
  1. SELECT
  2.    oc.customer_id
  3.   ,concat(oc.customer_fname,' ',oc.customer_lname) as Full_name
  4.   ,oc.customer_email
  5.   ,oc.customer_phone
  6.   ,a.country
  7. FROM online_customer oc
  8. JOIN address a ON oc.address_id = a.address_id
  9. WHERE oc.customer_id = (
  10.   SELECT
  11.     oh.customer_id
  12.  FROM order_header oh
  13.  WHERE oh.order_status = 'Cancelled'
  14. )
  15. GROUP BY oc.customer_id, concat(oc.customer_fname,' ', oc.customer_lname), oc.customer_email, oc.customer_phone, a.country;

There is a column in order_header table called Order_status which has values "Shipped" ,"In process" and "Cancelled". Now I want a customer_id who has all orders as "Cancelled" in order_status. I am not able to get the logic right

答案1

得分: 1

以下是您要翻译的代码部分：

  1. select oc.customer_id,concat(oc.customer_fname,' ',oc.customer_lname) as Full_name,oc.customer_email,oc.customer_phone,
  2. a.country
  3. from online_customer oc join address a on oc.address_id = a.address_id
  4. where oc.customer_id=(select oh.customer_id
  5. from order_header oh 
  6. group by customer_id 
  7. having count(distinct order_status) = 1 and max(order_status) = 'Cancelled')
  8. group by oc.customer_id,concat(oc.customer_fname,' ',oc.customer_lname),oc.customer_email,oc.customer_phone,a.country;
英文:

You can have this logic by grouping the orders table by the customer_id then checking it has only one distinct order_status, and thats the single status (using max/min) is 'Canceled'

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

huangapple
  • 本文由 发表于 2023年6月27日 19:20:18
  • 转载请务必保留本文链接：https://go.coder-hub.com/76564338.html
  • mysql
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定