sql练习及理解_2

junkaiw · 发表于 2020-12-6 11:15:41

马上注册，结交更多好友，享用更多功能^_^

您需要登录才可以下载或查看，没有账号？立即注册

x

本帖最后由 junkaiw 于 2020-12-7 10:29 编辑

登录/注册后可看大图

目录
1. 行转列，列转行问题
2. 根据成绩判断是否及格问题
3. 部门表和员工表匹配展示问题

登录/注册后可看大图

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);

示例数据展示：

登录/注册后可看大图

期望效果：

登录/注册后可看大图

方法一：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

登录/注册后可看大图

可以理解为每个学生的每门成绩我们都做出一个特定列来承载，如果不是所指定学科的成绩将置为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

示例数据展示：

登录/注册后可看大图

期望效果：

登录/注册后可看大图

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效率高

登录/注册后可看大图

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);

示例数据展示：

登录/注册后可看大图

期望效果：

登录/注册后可看大图

方法一：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处理

登录/注册后可看大图

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,'陈七');

登录/注册后可看大图

# 部门表
create table department(id int, department varchar(10));
insert into department values(1,'设计');
insert into department values(2,'市场');
insert into department values(3,'售后');

登录/注册后可看大图

期望结果：

登录/注册后可看大图

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。

账号		自动登录	找回密码
密码			立即注册

[见证历程] sql练习及理解_2

马上注册，结交更多好友，享用更多功能^_^