网站开发人员负责方面,网站成立时间查询,乡村网站建设,莆田自助建站软件力扣题
1、题目地址
1355. 活动参与者
2、模拟表
表#xff1a;Friends
Column NameTypeidintnamevarcharactivityvarchar
id 是朋友的 id#xff0c;并且在 SQL 中#xff0c;是该表的主键name 是朋友的名字activity 是朋友参加的活动的名字
表#xff1a;Activiti…力扣题
1、题目地址
1355. 活动参与者
2、模拟表
表Friends
Column NameTypeidintnamevarcharactivityvarchar
id 是朋友的 id并且在 SQL 中是该表的主键name 是朋友的名字activity 是朋友参加的活动的名字
表Activities
Column NameTypeidintnamevarchar
在 SQL 中id 是该表的主键name 是活动的名字
3、要求
找出那些既没有最多也没有最少参与者的活动的名字。
Activities 表中的任意活动都有在 Friends 中参与过。
可以以 任何顺序 返回结果。
下面是返回结果格式的例子。
示例 1
输入
Friends 表
idnameactivity1Jonathan D.Eating2Jade W.Singing3Victor J.Singing4Elvis Q.Eating5Daniel A.Eating6Bob B.Horse Riding
Activities 表
idname1Eating2Singing3Horse Riding
输出
activitySinging
解释 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 activity2、过滤操作我们知道使用 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 的用法 参考里面注意看评论参考里面最后一个例子看着是有误的
