要求:写一条 SQL 查询那些既没有最多,也没有最少参与者的活动的名字。
表: Friends的结构
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
| activity | varchar |
+---------------+---------+
id 是朋友的 id 和该表的主键
name 是朋友的名字
activity 是朋友参加的活动的名字
表: Activities的结构
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| name | varchar |
+---------------+---------+
id 是该表的主键
name 是活动的名字
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 |
+------+--------------+
Result Table:
+--------------+
| activity |
+--------------+
| Singing |
+--------------+
Eating 活动有三个人参加, 是最多人参加的活动 (Jonathan D. , Elvis Q. and Daniel A.)
Horse Riding 活动有一个人参加, 是最少人参加的活动 (Bob B.)
Singing 活动有两个人参加 (Victor J. and Jade W.)
SQL语句:
with c as(
select a.name as n1,count(b.id) as c1
from activities a
left join friends b
on a.name=b.activity
group by a.name
)
select n1 as activity from
c where c1>(select min(c1) from c)
and c1<(select max(c1) from c)