【MySQL】ANY函数 的巧用（筛选字段 ＞ ANY(语句) 和 筛选字段 ＜ ANY(语句)）

力扣题

1、题目地址

1355. 活动参与者

2、模拟表

表：Friends

• id 是朋友的 id，并且在 SQL 中，是该表的主键
• name 是朋友的名字
• activity 是朋友参加的活动的名字

表：Activities

• 在 SQL 中，id 是该表的主键
• name 是活动的名字

3、要求

找出那些既没有最多，也没有最少参与者的活动的名字。

Activities 表中的任意活动都有在 Friends 中参与过。

可以以 任何顺序 返回结果。

下面是返回结果格式的例子。

示例 1：

输入：

Friends 表：

Activities 表：

输出：

解释：
Eating 活动有三个人参加, 是最多人参加的活动 (Jonathan D. , Elvis Q. and Daniel A.)
Horse Riding 活动有一个人参加, 是最少人参加的活动 (Bob B.)
Singing 活动有两个人参加 (Victor J. and Jade W.)

4、代码编写

我的写法

代码

SELECT DISTINCT activity
FROM (SELECT *, COUNT(activity) OVER (PARTITION BY activity) AS numFROM Friends
) AS two
WHERE num != (SELECT MAX(num)FROM (SELECT *, COUNT(activity) OVER (PARTITION BY activity) AS numFROM Friends) AS one
) 
AND num != (SELECT MIN(num)FROM (SELECT *, COUNT(activity) OVER (PARTITION BY activity) AS numfrom 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 numFROM Friends) AS one
) 
AND num != (SELECT MIN(num)FROM (SELECT *, COUNT(activity) OVER (PARTITION BY activity) AS numfrom 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 的用法
参考里面注意看评论，参考里面最后一个例子看着是有误的