2084. 为订单类型为 0 的客户删除类型为 1 的订单

SQL架构

活动表: Orders

+-------------+------+
| Column Name | Type |
+-------------+------+
| order_id    | int  | 
| customer_id | int  |
| order_type  | int  | 
+-------------+------+
order_id是此表的主键列。
此表的每一行都表示订单的ID、订购该订单的客户的ID以及订单类型。
订单可以是类型0或类型1。

编写SQL查询以根据以下条件报告所有订单：

• 如果客户至少有一个类型为0的订单，则不要报告该客户的任何类型为1的订单。
• 否则，报告客户的所有订单。

按任意顺序返回结果表。

查询结果格式如下例所示。

示例 1:

输入: 
Orders table:
+----------+-------------+------------+
| order_id | customer_id | order_type |
+----------+-------------+------------+
| 1        | 1           | 0          |
| 2        | 1           | 0          |
| 11       | 2           | 0          |
| 12       | 2           | 1          |
| 21       | 3           | 1          |
| 22       | 3           | 0          |
| 31       | 4           | 1          |
| 32       | 4           | 1          |
+----------+-------------+------------+
输出: 
+----------+-------------+------------+
| order_id | customer_id | order_type |
+----------+-------------+------------+
| 31       | 4           | 1          |
| 32       | 4           | 1          |
| 1        | 1           | 0          |
| 2        | 1           | 0          |
| 11       | 2           | 0          |
| 22       | 3           | 0          |
+----------+-------------+------------+
解释: 
客户1有两个类型为0的订单。我们两个都返回。
客户2的订单类型为0，订单类型为1。我们只返回类型为0的订单。
客户3的订单类型为0，订单类型为1。我们只返回类型为0的订单。
客户4有两个类型1的订单。我们两个都返回。

# Write your MySQL query statement below
with  t1 as (select
customer_id #至少有一个类型为0的订单的客户
from
Orders
group by
customer_id
having
sum(if(order_type=0,1,0)) > 0
),
t2 as (
select
o.order_id,o.customer_id,o.order_type,t1.customer_id customer_id2
from
Orders o left join t1
using(customer_id)
)
select
order_id,customer_id,order_type
from
t2
where customer_id2 is null 
union  all
select
order_id,customer_id,order_type
from
t2
where customer_id2 is not null and order_type = 0

# Write your MySQL query statement below
with  t1 as (select
customer_id #至少有一个类型为0的订单的客户
from
Orders
group by
customer_id
having
sum(if(order_type=0,1,0)) > 0
),
t2 as (
select
o.order_id,o.customer_id,o.order_type,t1.customer_id customer_id2
from
Orders o left join t1
using(customer_id)
)
select
order_id,customer_id,order_type #没有类型为0的订单的用户的所有内容
from
t2
where customer_id2 is null 
union  all
select
order_id,customer_id,order_type #至少有一个类型为0的订单，不要报告该客户的任何类型为1的订单 的所有内容
from
t2
where customer_id2 is not null and order_type = 0