[MYSQL] where having group left join 面试题整理 （高富帅 农场主 燕十八老师 公益讲座 作业）
3.7:where-having-group综合练习题
有如下表及数据
+------+---------+-------+
| name | subject | score |
+------+---------+-------+
| 张三 | 数学 | 90 |
| 张三 | 语文 | 50 |
| 张三 | 地理 | 40 |
| 李四 | 语文 | 55 |
| 李四 | 政治 | 45 |
| 王五 | 政治 | 30 |
+------+---------+-------+
要求:查询出2门及2门以上不及格者的平均成绩
解题思路:
1. 查出所有人的平均成绩 select name,avg(score) from result group by name;
+------+------------+
| name | avg(score) |
+------+------------+
| 张三 | 60.0000 |
| 李四 | 50.0000 |
| 王五 | 30.0000 |
| 赵六 | 99.0000 |
+------+------------+
2. 查出2门及两门以上不及格的人
(把挂科的数目整理出来命名为 gks) select name, sum(score < 60) as gks from result group by name;
+------+------+
| name | gks |
+------+------+
| 张三 | 2 |
| 李四 | 2 |
| 王五 | 1 |
| 赵六 | 0 |
+------+------+
(按照挂科数整理出来)select name, sum(score < 60) as gks from result group by name having gks >=2;
+------+------+
| name | gks |
+------+------+
| 张三 | 2 |
| 李四 | 2 |
+------+------+
3.整合两者列出2门及2门以上不及格者的平均成绩.
+------+------------+------+
| name | avg(score) | gks |
+------+------------+------+
| 张三 | 60.0000 | 2 |
| 李四 | 50.0000 | 2 |
+------+------------+------+
面试题目
查出 2006-6-1 到2006-7-1之间举行的所有比赛，并且用以下形式列出：
拜仁 2：0 不来梅 2006-6-21
mysql> select * from match1;
+---------+------------+-------------+-------------+------------+
| matchID | hostteamID | guestteamID | matchresult | matchtime |
+---------+------------+-------------+-------------+------------+
| 1 | 1 | 2 | 2:0 | 2006-05-21 |
| 2 | 2 | 3 | 1:2 | 2006-06-21 |
| 3 | 3 | 1 | 2:5 | 2006-06-25 |
| 4 | 2 | 1 | 3:2 | 2006-07-21 |
+---------+------------+-------------+-------------+------------+
4 rows in set (0.00 sec)
mysql> select * from team;
+--------+----------+
| teamID | teamname |
+--------+----------+
| 1 | 北京国安 |
| 2 | 上海申花 |
| 3 | 公益连队 |
+--------+----------+
解题思路:
1. 使用两个左连接完成讲球队代码换成球队名字(注意第一次左连接 需要重命名字段 以免模糊)
select * from match1 left join team as t1 on hostteamID=teamID left join team on guestteamID = team.teamID ;
+---------+------------+-------------+-------------+------------+--------+----------+--------+----------+
| matchID | hostteamID | guestteamID | matchresult | matchtime | teamID | teamname | teamID | teamname |
+---------+------------+-------------+-------------+------------+--------+----------+--------+----------+
| 1 | 1 | 2 | 2:0 | 2006-05-21 | 1 | 北京国安 | 2 | 上海申花 |
| 2 | 2 | 3 | 1:2 | 2006-06-21 | 2 | 上海申花 | 3 | 公益连队 |
| 3 | 3 | 1 | 2:5 | 2006-06-25 | 3 | 公益连队 | 1 | 北京国安 |
| 4 | 2 | 1 | 3:2 | 2006-07-21 | 2 | 上海申花 | 1 | 北京国安 |
+---------+------------+-------------+-------------+------------+--------+----------+--------+----------+
2. 使用一个where 语句来过滤特定时间内的条目
mysql> select t1.teamname,matchresult,team.teamname,matchtime from
> match1 left join team as t1 on hostteamID=teamID
> left join team on guestteamID = team.teamID
> where matchtime between '2006-06-01' and '2006-07-01';
+----------+-------------+----------+------------+
| teamname | matchresult | teamname | matchtime |
+----------+-------------+----------+------------+
| 上海申花 | 1:2 | 公益连队 | 2006-06-21 |
| 公益连队 | 2:5 | 北京国安 | 2006-06-25 |
+----------+-------------+----------+------------+
