Medium之1355.活动参与者——查找不是最大也不是最小的值

港控/mmm° 2022-08-30 11:22 95阅读 0赞

**表: Friends**

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>name</td> 
   <td>varchar</td> 
  </tr> 
  <tr> 
   <td>activity</td> 
   <td>varchar</td> 
  </tr> 
 </tbody> 
</table>

id 是朋友的 id 和该表的主键  
name 是朋友的名字  
activity 是朋友参加的活动的名字

**表: Activities**

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>name</td> 
   <td>varchar</td> 
  </tr> 
 </tbody> 
</table>

id 是该表的主键  
name 是活动的名字

### 问题 ###

写一条 SQL 查询那些既没有最多，也没有最少参与者的活动的名字

可以以任何顺序返回结果，Activities 表的每项活动的参与者都来自 Friends 表  
注意：名称相同 id 不同的参与者算作两个人

### 示例 ###

**Friends 表:**

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>activity</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Jonathan D.</td> 
   <td>Eating</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Jade W.</td> 
   <td>Singing</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Victor J.</td> 
   <td>Singing</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Elvis Q.</td> 
   <td>Eating</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>Daniel A.</td> 
   <td>Eating</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>Bob B.</td> 
   <td>Horse Riding</td> 
  </tr> 
 </tbody> 
</table>

**Activities 表:**

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Eating</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Singing</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Horse Riding</td> 
  </tr> 
 </tbody> 
</table>

**Result 表:**

<table> 
 <thead> 
  <tr> 
   <th>activity</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Singing</td> 
  </tr> 
 </tbody> 
</table>

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

来源：力扣（LeetCode）  
链接：https://leetcode-cn.com/problems/activity-participants  
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

### 解答 ###

##### 法一：NOT IN法 #####

select activity
     from friends
     group by activity
     having count(*) not in(
                            (select count(activity)
                            from friends
                            group by activity
                            order by count(activity) desc
                            limit 1),
                            (select count(activity)
                            from friends
                            group by activity
                            order by count(activity) asc
                            limit 1)
                            )

##### 法二：巧用>any和<any法 #####

select activity from friends
    group by activity
    having count(activity)>any(select count(activity) from friends group by activity)
           and count(activity) < any(select count(activity) from friends group by activity)