「这是我参与2022首次更文挑战的第13天，活动详情查看：2022首次更文挑战」
分享三道最近时间联系sql中比较有趣的题目
M-统计每个老师教授课程的学生总数-if(expr1,expr2)
预期结果
分析过程
先了解一个判断函数IFNULL(expr1,expr2)
假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语境。
特别注意 sql 5.6和5.7之后Group by用法
这里的group by后面如果直接接t.name的话，可能会因为同名教师出现错误，正确的做法还是使用teacher_id进行分组，
但是在SQL5.7之后是不允许group by id, select name字段，输出却包含name字段
所以为了保险起见，还是应该写group by t.id,t.name
因此sql应该这样写
SELECT t.name AS teacher_name,
sum(if(teacher_id is null,0 ,student_count)) as
student_countFROM teachers t LEFTJOIN courses c
ON t.id=c.teacher_id GROUPBY t.id,t.name
否则容易出现查询sql语句报错信息：
Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause ..............
问题原因：
MySQL 5.7.5和up实现了对功能依赖的检测。如果启用了only_full_group_by SQL模式(在默认情况下是这样)，那么MySQL就会拒绝选择列表、条件或顺序列表引用的查询，这些查询将引用组中未命名的非聚合列，而不是在功能上依赖于它们。
(在5.7.5之前，MySQL没有检测到功能依赖项，only_full_group_by在默认情况下是不启用的。关于前5.7.5行为的描述，请参阅MySQL 5.6参考手册。)
删除重复的（ID小的数字）
编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
Id 是这个表的主键
预期结果
例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
分析过程：
方法一：
使用 DELETE 和 WHERE 子句
我们可以使用以下代码，将此表与它自身在电子邮箱列中连接起来。
SELECT p1.* FROM Person p1, Person p2 WHERE p1.Email = p2.Email;
然后我们需要找到其他记录中具有相同电子邮件地址的更大 ID。所以我们可以像这样给 WHERE 子句添加一个新的条件。
DELETE p1 FROM Person p1, Person p2 WHERE p1.Email = p2.Email AND p1.Id > p2.Id
此方法虽然理解上比较容易，但是做了自链接导致用时较长，提交的大概都在900ms左右
所以有了方法二
方法二（通过中间表）：
- 先通过查询下最小的id 值
- 通过中间查询
- 再删除id 不在这范围的值
因此上述的题目还可以改写成
delete from Person where id not in(SELECT * from (SELECT min(id ) from Person group by Email)t)
题目简述
查询 teachers 表中，筛选出该国家教师的平均年龄大于所有国家教师的平均年龄的国家，查询出这些国家的教师信息。
预期结果
分析过程
- 筛选出该国家教师的平均年龄 这里需要先进行根据国家分组，求出平均成绩
SELECT country FROM teachers group by country
大于
- 所有国家教师的平均年龄的国家
SELECT avg(age) FROM teachers
- 查询出这些国家的教师信息。
WHERE country in
SQL
-- 查询并返回所有符合要求的老师信息 --
SELECT* FROM teachers
WHERE country in
(SELECT country FROM teachers group by country
having avg(age) >(SELECTavg(age) FROM teachers));
跋尾
本篇内容就到这里了~ 我是Zeus👩🏻🚀来自一个互联网底层组装员，下一篇再见！ 📖
