【MySQL】ANY函数的巧用

力扣题

1、题目地址

1355. 活动参与者

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 中参与过。

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

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

示例 1：

输入：

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.)

4、代码编写

我的写法

代码

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 的用法
参考里面注意看评论，参考里面最后一个例子看着是有误的