※ 商品专题

标签：customer product 专题 name distinct 商品 id select

涉及多种商品的差异、包含等问题。

1. 包含问题

通用方法为：self join --> group by --> having count()

力扣1045：买下所有产品的客户

# 方法：self join
select distinct customer_id
from Customer left join Product
on Customer.product_key=Product.product_key
group by customer_id
having count(distinct Product.product_key)=
    (select count(distinct product_key)
    from Product)

力扣1398：购买了产品 A 和产品 B 却没有购买产品 C 的顾客

方法一：self join，务必先预处理除掉其他字段
with tmp0 as
(select distinct customer_id,product_name
from Orders)

,tmp1 as
(select 'A' as product_name 
union all
select 'B' as product_name)

,tmp2 as
(select 'A' as product_name 
union all
select 'B' as product_name
union all
select 'C' as product_name)

,tmp3 as
(select customer_id
from tmp0 left join tmp1
on tmp0.product_name=tmp1.product_name
group by customer_id
having count(tmp1.product_name)=2)

,tmp4 as
(select customer_id
from tmp0 left join tmp2
on tmp0.product_name=tmp2.product_name
group by customer_id
having count(tmp2.product_name)=3)

,tmp5 as
(select distinct customer_id
from tmp3
where customer_id not in
    (select distinct customer_id
    from tmp4))

select distinct tmp5.customer_id,customer_name
from Customers join tmp5
on Customers.customer_id=tmp5.customer_id
order by customer_id

# 方法二：group by, having
select distinct Customers.customer_id,customer_name
from Customers,Orders
where Customers.customer_id=Orders.customer_id
group by Customers.customer_id
having Orders.product_name in('A','B')
and Orders.product_name<>'C'
order by Customers.customer_id

2. 差异问题（比如关于不同商品数量的差异）

力扣1445：

# 方法一：self join
select distinct s1.sale_date,s1.sold_num-s2.sold_num as diff
from Sales s1,Sales s2
where s1.sale_date=s2.sale_date
and s1.fruit<s2.fruit

# 方法二：self join
select s1.sale_date,s1.sold_num-s2.sold_num as diff         
from Sales s1,Sales s2
where s1.sale_date=s2.sale_date
and s1.fruit='apples'
and s2.fruit='oranges'

# 方法三：lag
with tmp as
(select *,
    lag(sold_num,1,0) over(partition by sale_date order by fruit) 
    -sold_num as diff
from Sales)

select sale_date,diff
from tmp
where fruit='oranges'
order by sale_date

-END

标签：customer,product,专题,name,distinct,商品,id,select
From： https://www.cnblogs.com/peitongshi/p/16972723.html