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