力扣题
1、题目地址
2、模拟表
表:Friends
Column Name | Type |
---|---|
id | int |
name | varchar |
activity | varchar |
- id 是朋友的 id,并且在 SQL 中,是该表的主键
- name 是朋友的名字
- activity 是朋友参加的活动的名字
表:Activities
Column Name | Type |
---|---|
id | int |
name | varchar |
- 在 SQL 中,id 是该表的主键
- name 是活动的名字
3、要求
- 找出那些既没有最多,也没有最少参与者的活动的名字。
- Activities 表中的任意活动都有在 Friends 中参与过。
- 可以以 任何顺序 返回结果。
4、示例
输入:
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 |
输出:
activity |
---|
Singing |
解释:
Eating 活动有三个人参加, 是最多人参加的活动 (Jonathan D. , Elvis Q. and Daniel A.)
Horse Riding 活动有一个人参加, 是最少人参加的活动 (Bob B.)
Singing 活动有两个人参加 (Victor J. and Jade W.)
5、代码编写
我的写法
代码
SELECT DISTINCT activity
FROM (
SELECT *, COUNT(activity) OVER (PARTITION BY activity) AS num
FROM Friends
) AS two
WHERE num != (
SELECT MAX(num)
FROM (
SELECT *, COUNT(activity) OVER (PARTITION BY activity) AS num
FROM Friends
) AS one
)
AND num != (
SELECT MIN(num)
FROM (
SELECT *, COUNT(activity) OVER (PARTITION BY activity) AS num
from Friends
) AS one
)
代码分析
1、先将出现次数算出来
SELECT *, COUNT(activity) OVER (PARTITION BY activity) AS num
FROM Friends
| id | name | activity | num |
| -- | ----------- | ------------ | --- |
| 1 | Jonathan D. | Eating | 3 |
| 4 | Elvis Q. | Eating | 3 |
| 5 | Daniel A. | Eating | 3 |
| 6 | Bob B. | Horse Riding | 1 |
| 2 | Jade W. | Singing | 2 |
| 3 | Victor J. | Singing | 2 |
2、之后再把最高次数和最低次数过滤掉
WHERE num != (
SELECT MAX(num)
FROM (
SELECT *, COUNT(activity) OVER (PARTITION BY activity) AS num
FROM Friends
) AS one
)
AND num != (
SELECT MIN(num)
FROM (
SELECT *, COUNT(activity) OVER (PARTITION BY activity) AS num
from Friends
) AS one
)
网友巧用 ANY 函数写法
代码
SELECT activity
FROM Friends
GROUP BY activity
HAVING COUNT(*) < ANY(SELECT COUNT(activity) OVER (PARTITION BY activity) FROM Friends)
AND COUNT(*) > ANY(SELECT COUNT(activity) OVER (PARTITION BY activity) FROM Friends)
代码分析
1、首先要求里面我们是要查询出活动名(activity),可以直接使用分组(GROUP BY)取出每个都单一,不用去重,之后就要进行过滤操作
SELECT activity
FROM Friends
GROUP BY activity
2、过滤操作,我们知道使用 GROUP BY 之后,使用 count(*) 可以获取对应分组里面出现的次数,我们只要满足让次数不为全部的最大和全部的最小即可
SELECT COUNT(activity) OVER (PARTITION BY activity) FROM Friends
我使用 SELECT *, COUNT(activity) OVER (PARTITION BY activity) AS num FROM Friends
执行让大家看明显一些
| id | name | activity | num |
| -- | ----------- | ------------ | --- |
| 1 | Jonathan D. | Eating | 3 |
| 4 | Elvis Q. | Eating | 3 |
| 5 | Daniel A. | Eating | 3 |
| 6 | Bob B. | Horse Riding | 1 |
| 2 | Jade W. | Singing | 2 |
| 3 | Victor J. | Singing | 2 |
可以看出出现次数最大值是3,最小值是1
3、这里就可以使用到 ANY 函数,上面的 < ANY 表示,次数小于右边的最大值(3),下面 > ANY 表示,次数大于右边的最小值
COUNT(*) < ANY(SELECT COUNT(activity) OVER (PARTITION BY activity) FROM Friends)
AND COUNT(*) > ANY(SELECT COUNT(activity) OVER (PARTITION BY activity) FROM Friends)
具体可参考:MySQL 中 ALL 和 ANY 的用法
参考里面注意看评论,参考里面最后一个例子看着是有误的