sql练习及理解_2
本帖最后由 junkaiw 于 2020-12-7 10:29 编辑static/image/hrline/1.gifstatic/image/hrline/1.gif
目录
1. 行转列，列转行问题
2. 根据成绩判断是否及格问题
3. 部门表和员工表匹配展示问题
static/image/hrline/1.gifstatic/image/hrline/1.gif
1. 行转列，列转行问题
[*]行转列
数据准备：
create table course_01 (name varchar(30), course varchar(30), score int);
insert into course_01 values ('jerry', 'math', 69);
insert into course_01 values ('jerry', 'english', 89);
insert into course_01 values ('cc', 'math', 49);
insert into course_01 values ('cc', 'english', 99);
示例数据展示：
https://xxx.ilovefishc.com/album/202012/05/215825fhgchsczitws6lhi.png
期望效果：
https://xxx.ilovefishc.com/album/202012/05/215846r4m6n7uqfz7mrmrq.png
方法一：case-when
select name,
sum(CASE course WHEN 'math' THEN score ELSE 0 END) as math,
sum(CASE course WHEN 'english' THEN score ELSE 0 END) as english
FROM course_01
group by name
重点知识整理：
步骤理解：
1. 首先转换前和转换后name列是不会改变的，所以select 后面肯定需要有name
2. 其次如果不添加聚合函数sum，也不groupby，我们会得到一下结果：
select name,
(CASE course WHEN 'math' THEN score ELSE 0 END) as math,
(case course when 'english' THEN score else 0 end) as english
FROM course_01
https://xxx.ilovefishc.com/album/202012/05/220725o4k2cj5vxjddeixi.png
可以理解为每个学生的每门成绩我们都做出一个特定列来承载，如果不是所指定学科的成绩将置为0
3. 增加sum聚合函数是为了使得groupby后一个学生一门课程只能有一个成绩（不可能一会是0一会又不是）
方法二：if函数
SELECT name,
sum(if(course = 'math',score,0)) as math,
sum(if(course = 'english', score,0))as english
from course_01
group by name
整体思路其实是差不多的，只不过用if函数代替了case when
[*]列转行
数据准备：（将之前生成的行转列结果存入新表）
create table course_02 as
select name,
sum(CASE course WHEN 'math' THEN score ELSE 0 END) as math,
sum(case course when 'english' THEN score else 0 end) as english
FROM course_01
group by name
示例数据展示：
https://xxx.ilovefishc.com/album/202012/05/215846r4m6n7uqfz7mrmrq.png
期望效果：
https://xxx.ilovefishc.com/album/202012/05/215825fhgchsczitws6lhi.png
select name, 'math' as course, math as score from course_02
union all
select name, 'english' as course, english as score from course_02
重点知识整理：
1. 'math' as course 这个是增加了一个列，列名为course数据全部为‘math’
2. union all 和union 的区别:
union all 不会去重，union会去重
union会排序，union all只合并结果不排序
union all效率高
static/image/hrline/5.gifstatic/image/hrline/5.gifstatic/image/hrline/5.gif
2. 根据成绩判断是否及格问题
数据准备：
# 判断是否及格
create table score_04(course varchar(10), score int);
insert into score_04 VALUES('java', 70);
insert into score_04 VALUES('sql', 90);
insert into score_04 VALUES('python',30);
示例数据展示：
https://xxx.ilovefishc.com/album/202012/05/233245v9u53f685agom5u1.png
期望效果：
https://xxx.ilovefishc.com/album/202012/05/233304rzbroohok1nbnnqk.png
方法一：case-when
select course,score,
(case when score >=60 then 'pass' else 'fail' end ) as result
from score_04
重点知识整理：
case when使用方法：
1. 多个字段的判断，case 后不指定字段，使用when来判断多个字段
case when ... then ... else ... end as列名
2. 单个字段的判断，case 接固定字段，按照值处理
case 字段 when .... then ... else ... end as列名
3. 多条件：
case
when .... then...
when .... then...
else ..
end as 列名
方法二：if函数
select *, (if(score >=60, 'pass', 'fail')) as result
from score_04
说明：如果出现多条件，通常不用if处理
static/image/hrline/5.gifstatic/image/hrline/5.gifstatic/image/hrline/5.gif
3. 部门表和员工表匹配展示问题
有两张表分别记录了部门情况，和员工情况，现需要根据部门id将两表关联展示员工、所在部门的信息。
数据准备：
# 员工表
drop table if EXISTS staff;
create table staff(dptid int, name varchar(12));
insert into staff values(1,'张三');
insert into staff values(1,'李四');
insert into staff values(2,'王五');
insert into staff values(3,'彭六');
insert into staff values(4,'陈七');
https://xxx.ilovefishc.com/album/202012/06/033145k7nxrw2n7aiuwcc3.png
# 部门表
create table department(id int, department varchar(10));
insert into department values(1,'设计');
insert into department values(2,'市场');
insert into department values(3,'售后');
https://xxx.ilovefishc.com/album/202012/06/033156uno647qjbjtn3ldw.png
期望结果：
https://xxx.ilovefishc.com/album/202012/06/033254t22jyai7os6qj44a.png
select ROW_NUMBER()over()as id, a.dptid as dptID, ifnull(b.department,'无')as department,a.name
from staff a
left join department b
on a.dptid = b.id
重点知识整理：
1. 由于一般公司内部部门相关固定，但是有可能有员工没有部门，所以使用左连接
2. 由于本身表中没有员工编号，所以使用row_number对每个员工进行编号（假设不存在重复数据）
如果存在重复数据：
select ROW_NUMBER()over()as id, c.* from
(select DISTINCT a.dptid as dptID, ifnull(b.department,'无')as department,a.name
from staff a
left join department b
on a.dptid = b.id) as c
distinct 必须放在查询语句最前，所以需要先进行去重，然后在添加序号。
3.对一些关于操作null的函数的总结
-- isnull(exper) 判断exper是否为空，是则返回1，否则返回0
-- ifnull(exper1,exper2)判断exper1是否为空，是则用exper2代替
-- nullif(exper1,exper2)如果expr1= expr2 成立，那么返回值为NULL，否则返回值为 expr1。
页:
[1]