【MySQL】ANY函数的巧用

发布时间:2024年01月09日

力扣题

1、题目地址

1355. 活动参与者

2、模拟表

表:Friends

Column NameType
idint
namevarchar
activityvarchar
  • id 是朋友的 id,并且在 SQL 中,是该表的主键
  • name 是朋友的名字
  • activity 是朋友参加的活动的名字

表:Activities

Column NameType
idint
namevarchar
  • 在 SQL 中,id 是该表的主键
  • name 是活动的名字

3、要求

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

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

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

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

示例 1:

输入:

Friends 表:

idnameactivity
1Jonathan D.Eating
2Jade W.Singing
3Victor J.Singing
4Elvis Q.Eating
5Daniel A.Eating
6Bob B.Horse Riding

Activities 表:

idname
1Eating
2Singing
3Horse 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 的用法
参考里面注意看评论,参考里面最后一个例子看着是有误的

文章来源:https://blog.csdn.net/weixin_50223520/article/details/135479980
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。