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
细节:去重问题,排序输出问题