1355. 活动参与者---inner join 或窗口函数

最新推荐文章于 2022-06-12 18:18:28 发布

铁锤2号

最新推荐文章于 2022-06-12 18:18:28 发布

阅读量109

点赞数

分类专栏：力扣数据库题

本文链接：https://blog.csdn.net/zr1213159840/article/details/117440650

版权

力扣数据库题专栏收录该内容

35 篇文章 1 订阅

订阅专栏

活动参与者
题目分析：题目要求出不是最多也不是最少人参加的活动，那咱们直接排除就好了。排除方法有两种，一种可以是使用limit 限制，然后取交集，即inner join。还有一种就可以使用dense_rank（）
解法一

select F1.activity from (
select activity from Friends group by activity having count(*) != (select count(*) num from Friends group by activity order by num desc limit 1)) F1 inner join 

(select activity from Friends group by activity having count(*) != (select count(*) num from Friends group by activity order by num limit 1) ) F2 on F1.activity = F2.activity;

解法二

select f.activity from (
select activity,DENSE_RANK() over(ORDER BY count(activity) desc) d1_rank,DENSE_RANK() over(ORDER BY count(activity)) d2_rank from Friends group by activity ) f where d1_rank <> 1 and d2_rank <> 1;