力扣SQL刷题3

1082. 销售分析 I

题型：输出A列不同种类中对应的B值和，输出多个并列第一的项
解题： 因为可能有并列–不能order by后用limit 1
考虑用rank()over(order by sum(列B) desc) 和group by 列A ---- 再select嵌套一下后筛选rk = 1
不用rank()over(partition by 列A order by sum(列B) desc ) 因为这样的排序是在不同A种类中进行排序
顺序： from – group by 最后的select

题目：

select seller_id from 
(select seller_id,
rank()over(order by sum(price) desc) as rk
from Sales
group by seller_id) a 
where rk = 1

1083. 销售分析 II

题型：对列A中不同类别分组中，列B中有值1但没用值2的，列A项
解答1：从含有值1的项中删除含有值2的 — where中筛选出来，然后not in一下
解答2：用sum判断，符合值为1不符合为0 ---- group by后sum(列B = 值1)>0 and sum(列B = 值2)=0

题目：

解答1：从含有值1的项中删除含有值2的
因为筛选出含有值1的，直接where 列B=值1，select就能出来，所以不含有的就not in 掉

select distinct s.buyer_id
from Sales s join Product p on s.product_id = p.product_id
where s.buyer_id not in
	(select distinct s.buyer_id from Sales s join Product p on s.product_id = p.product_id 
	where p.product_name = 'iphone')
and p.product_name = 'S8'

解答2：用sum判断，符合值为1不符合为0 — group by后sum

select  distinct s.buyer_id
from Sales s join Product p on s.product_id = p.product_id
group by s.buyer_id
having sum(p.product_name = 'S8')>0 and sum(p.product_name = 'iphone') =0

**group by 后，sum(if(条件，1，0))**再与0判断也很不错

GROUP BY buyer_id
HAVING SUM(IF(p1.product_name = 'S8', 1, 0)) > 0 
    AND SUM(IF(p1.product_name = 'iPhone', 1, 0)) = 0

1084. 销售分析III

题型：对列A中的各种类别，列B中的值在一个区间范围内的 输出
解答：group by 列A 后having max(列B)<区间上限 and min(列B)>区间下限

题目：

select 
from Sales s join Product p on on s.product_id =p.product_id
group by p.product_id,p.product_name
having min(s.sale_date)>="2019-01-01" and max(s.sale_date)<="2019-03-31"

2019-07-27（含）的前 30 天

1，where activity_date between ‘2019-06-28’ and ‘2019-07-27’
2，WHERE DATEDIFF(‘2019-07-27’,activity_date)<30

1148. 文章浏览 I

题型：每一行中，列A值和列B值相对的行输出
解答：where 列A = 列B 是可以的

题目：

select distinct author_id as id
from Views 
where author_id = viewer_id
order by author_id

1149. 文章浏览 II

题型：某人某一天满足了某条件
解答：group by 人列，天列
having 条件

题目：

select distinct viewer_id as id
from Views
group by viewer_id,view_date
having count(distinct article_id) >1
order by viewer_id

细节：去重问题，排序输出问题