这两天在写一个权限的sql, 涉及 3 张表, 然后做了一个 union all 的操作, 感觉效率有点问题, 写套娃, 改来改去的做优化. 关键数据又不能贴, 嗯, 还是明天搞个假数据来说明这个关系. (PS. 多次测试出一个结论, 关于 mysql 的, select * 和 select 某字段, 二者性能无明显差异), 原因是, sql 执行的顺序是从 from ... where ... 这样开始的. select 的影响其实并不大.
SQL 顺序
写法: select > from > where > group by > having > order by > limit ...
执行: from > where > group by > having > select > order by > limit
还是先整理下, 有空再给贴一版假数据吧, 继续练习 sql 不能停.
表关系
需求 01
按平均成绩, 降序显示所有学生 的 所有课程的成绩, 以及平均成绩
分析
直接一步步来就行了,都分别给查询出来, 然后关联呀.
首先, 先查询一波 score 表的所有信息.
select * from score;
+------+------+-------+
| s_id | c_id | score |
+------+------+-------+
| 0001 | 0001 | 80 |
| 0001 | 0002 | 90 |
| 0001 | 0003 | 99 |
| 0002 | 0002 | 60 |
| 0002 | 0003 | 80 |
| 0003 | 0001 | 80 |
| 0003 | 0002 | 80 |
| 0003 | 0003 | 80 |
+------+------+-------+
8 rows in set (0.00 sec)
然后, 再根据 学号 做 group by 求出每个人的 平均成绩.
select
s_id,
avg(score)
from score
group by s_id
一定要注意, group by 后, 前面 select 的字段, 只能是 group by 中出现的, 或 聚合函数的字段, where 是在 group by 之前, 分组后的过滤是 having. 这些都是我这新手常写着写着就给弄错了.
+--------+--------------+
| 学号 | 平均成绩 |
+--------+--------------+
| 0001 | 89.6667 |
| 0002 | 70.0000 |
| 0003 | 80.0000 |
+--------+--------------+
3 rows in set (0.00 sec)
然后, 以第一张表为主表, 通过 学号, 将第二表给 "inner join " 拼接上.
select
a.*,
b.*
from score as a
inner join (
-- 小表作为一个子查询
select
s_id,
avg(score)
from score
group by s_id
) as b
on a.s_id = b.s_id
+------+------+-------+------+------------+
| s_id | c_id | score | s_id | avg(score) |
+------+------+-------+------+------------+
| 0001 | 0001 | 80 | 0001 | 89.6667 |
| 0001 | 0002 | 90 | 0001 | 89.6667 |
| 0001 | 0003 | 99 | 0001 | 89.6667 |
| 0002 | 0002 | 60 | 0002 | 70.0000 |
| 0002 | 0003 | 80 | 0002 | 70.0000 |
| 0003 | 0001 | 80 | 0003 | 80.0000 |
| 0003 | 0002 | 80 | 0003 | 80.0000 |
| 0003 | 0003 | 80 | 0003 | 80.0000 |
+------+------+-------+------+------------+
8 rows in set (0.00 sec)
然后再 按 avg(score) 降序即可.
select
a.*,
b.*
from score as a
inner join (
select
s_id,
avg(score) as avg_score
from score
group by s_id
) as b
on a.s_id = b.s_id
order by b.avg_score desc;
+------+------+-------+------+-----------+
| s_id | c_id | score | s_id | avg_score |
+------+------+-------+------+-----------+
| 0001 | 0001 | 80 | 0001 | 89.6667 |
| 0001 | 0002 | 90 | 0001 | 89.6667 |
| 0001 | 0003 | 99 | 0001 | 89.6667 |
| 0003 | 0003 | 80 | 0003 | 80.0000 |
| 0003 | 0001 | 80 | 0003 | 80.0000 |
| 0003 | 0002 | 80 | 0003 | 80.0000 |
| 0002 | 0002 | 60 | 0002 | 70.0000 |
| 0002 | 0003 | 80 | 0002 | 70.0000 |
+------+------+-------+------+-----------+
8 rows in set (0.00 sec)
.... 似乎不是这样子的, 应该是要给平铺开来哦, 序号, 每门课, 成绩, 平均分嘛..
学号, 课程1, 课程2, 课程3, 平均分
0001, 90, 80, 80, 83.33333
select
s_id as "学号",
avg(score) as "平均成绩"
from score
group by s_id
order by avg(score) desc;
+--------+--------------+
| 学号 | 平均成绩 |
+--------+--------------+
| 0002 | 70.0000 |
| 0003 | 80.0000 |
| 0001 | 89.6667 |
+--------+--------------+
3 rows in set (0.00 sec)
要把 c_id 给加进来, 用过 group by 了嘛, 就不能直接加了哦, , 但可以用 聚合函数 + case when 的方式来弄呀.
select
s_id as "学号",
-- max, min 都行的, group by s_id 了, 就一条记录
max(case when c_id="0001" then score else null end) as "语文",
min(case when c_id="0002" then score else null end) as "数学",
max(case when c_id="0003" then score else null end) as "英文",
avg(score) as "平均成绩"
from score
group by s_id
order by avg(score) desc;
+--------+--------+--------+--------+--------------+
| 学号 | 语文 | 数学 | 英文 | 平均成绩 |
+--------+--------+--------+--------+--------------+
| 0001 | 80 | 90 | 99 | 89.6667 |
| 0003 | 80 | 80 | 80 | 80.0000 |
| 0002 | NULL | 60 | 80 | 70.0000 |
+--------+--------+--------+--------+--------------+
3 rows in set (0.00 sec)
因此,这个练习的关键点在于 case when 条件为真 then xxx else yyy end 和 group by 的特点, 即用了 group by 后, select 只能放 group by 中出现的字段 或者聚合函数. 那这里就 这里用了小技巧, 取一条数据吗, 本来就一条,, 那就 直接 max() 或者 min() 都是一样的效果哦.
case when ..then..else ..end 跟 if ... else 的区别在于, case 用来查询字段, if ... else 用在存储过程, 自定义函数中等, 不能混用.
小结
- 多对一 这种查询集关系, 相同 id 关联, 其实用 多的一方, 来 inner 或者 left join 就给 合并一起了.
- 用了 group by 后, 前面的 select 中只能出现 group by 中的字段 或者 聚合函数,不然报错或没有意义,反误导
- 多分类平铺开, 可用 case when 条件为真 then xxx else yyy end 结合聚合函数实现.