Leetcode1355. 活动参与者(中等)
2020-07-22 本文已影响0人
kaka22
题目
表: Friends
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
| activity | varchar |
+---------------+---------+
id 是朋友的 id 和该表的主键
name 是朋友的名字
activity 是朋友参加的活动的名字
表: Activities
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
+---------------+---------+
id 是该表的主键
name 是活动的名字
写一条 SQL 查询那些既没有最多,也没有最少参与者的活动的名字
可以以任何顺序返回结果,Activities 表的每项活动的参与者都来自 Friends 表
下面是查询结果格式的例子:
Friends 表:
+------+--------------+---------------+
| id | name | activity |
+------+--------------+---------------+
| 1 | Jonathan D. | Eating |
| 2 | Jade W. | Singing |
| 3 | Victor J. | Singing |
| 4 | Elvis Q. | Eating |
| 5 | Daniel A. | Eating |
| 6 | Bob B. | Horse Riding |
+------+--------------+---------------+
Activities 表:
+------+--------------+
| id | name |
+------+--------------+
| 1 | Eating |
| 2 | Singing |
| 3 | Horse Riding |
+------+--------------+
Result 表:
+--------------+
| results |
+--------------+
| Singing |
+--------------+
Eating 活动有三个人参加, 是最多人参加的活动 (Jonathan D. , Elvis Q. and Daniel A.)
Horse Riding 活动有一个人参加, 是最少人参加的活动 (Bob B.)
Singing 活动有两个人参加 (Victor J. and Jade W.)
解答
其实就是掐头去尾
先把最多的和最少的活动选出来
-- 最多的
select F.activity
from Friends as F
group by F.activity
order by count(F.id) desc
limit 0,1;
-- 最少的
select F.activity
from Friends as F
group by F.activity
order by count(F.id) asc
limit 0,1;
排除这两个即可
select FF.activity
from Friends as FF
where FF.activity <>(select F.activity
from Friends as F
group by F.activity
order by count(F.id) desc
limit 0,1)
and FF.activity <>(select F.activity
from Friends as F
group by F.activity
order by count(F.id) asc
limit 0,1)
别的方法
select activity
from friends
group by activity
having count(*) between
(select count(*)+1 a from friends group by activity order by a asc limit 1)
and
(select count(*)-1 a from friends group by activity order by a desc limit 1);