1045. 买下所有产品的客户 1068. 产品销售分析 I 1069. 产品销售分析 II 1070. 产品销售分析 III

1045. 买下所有产品的客户

【select
元素开头可用distinct进行限制，但是不可以在连元素中间进行distinct限制，另外可在count中进行distinct】

• 如： select min(customer_id) , distinct product_key [错] select

  distinct min(customer_id) , product_key [对] select min(customer_id) customer_id, product_key,count( distinct
  product_key) num [对]

【# 因为min只会选择出它这一列最小值，其他列不会跟着走,所以最外层的查找该行是有必要的】

# Write your MySQL query statement below
select customer_id
from(
select  min(customer_id) customer_id, product_key,count(distinct  product_key) num
from Customer C
group by customer_id 
having num = (select count(*) from Product)
)new_table

1068. 产品销售分析 I【过于简单】

# Write your MySQL query statement below
select product_name ,year,price
from Sales ,Product
where Sales.product_id = Product.product_id

1069. 产品销售分析 II【过于简单】

select product_id ,sum(quantity) total_quantity
from Sales 
group by Sales.product_id

---

1070. 产品销售分析 III

---

# Write your MySQL query statement below
select product_id , year first_year,quantity,price 
from Sales
where (product_id,year)in(
select product_id , min(year) 
# 因为min只会选择出它这一列最小值，其他列不会跟着走,所以最外层的查找该行是有必要的
from Sales
group by product_id )