sql练习及理解_3
本帖最后由 junkaiw 于 2020-12-7 10:30 编辑static/image/hrline/1.gifstatic/image/hrline/1.gif
目录
1. 分组比较类问题
2.删除重复数据问题
3.日期比较问题
static/image/hrline/1.gifstatic/image/hrline/1.gif
1. 分组比较类问题
有一张学生成绩表，其中包含学生编号，课程编号，成绩；还有一张课程表，其中包含课程编号，课程名称。求每门课程编号，课程名称以及每门课程最高的成绩。
数据准备：
create table 课程(
课程编号 varchar(10),
课程名称 nvarchar(10),
教师编号 varchar(10));
insert into 课程 values('01' , '语文' , '02');
insert into 课程 values('02' , '数学' , '01');
insert into 课程 values('03' , '英语' , '03');
create table `成绩`
(`学生编号` varchar(10),
`课程编号` varchar(10),
`成绩` decimal(18,1));
insert into `成绩` values('01' , '01' , 80);
insert into `成绩` values('01' , '02' , 90);
insert into `成绩` values('01' , '03' , 99);
insert into `成绩` values('02' , '01' , 70);
insert into `成绩` values('02' , '02' , 60);
insert into `成绩` values('02' , '03' , 80);
insert into `成绩` values('03' , '01' , 80);
insert into `成绩` values('03' , '02' , 80);
insert into `成绩` values('03' , '03' , 80);
insert into `成绩` values('04' , '01' , 50);
insert into `成绩` values('04' , '02' , 30);
insert into `成绩` values('04' , '03' , 20);
insert into `成绩` values('05' , '01' , 76);
insert into `成绩` values('05' , '02' , 87);
insert into `成绩` values('06' , '01' , 31);
insert into `成绩` values('06' , '03' , 34);
insert into `成绩` values('07' , '02' , 89);
insert into `成绩` values('07' , '03' , 98);
https://xxx.ilovefishc.com/album/202012/07/092542sj7leej6noe4yjnk.png
https://xxx.ilovefishc.com/album/202012/07/092537fi3a52wgl4jh3tuj.png
期望效果：
https://xxx.ilovefishc.com/album/202012/07/092548ibs3s0s3s33xe010.png
select distinct a.课程编号, a.课程名称, b.成绩
from 课程 as a join 成绩 as b
on a.课程编号 = b.课程编号
where (b.课程编号, b.成绩) in (
SELECT 课程编号, max(成绩)
from 成绩 GROUP BY 课程编号);
说明：
使用distinct用于去重，避免一门课出现多个最高分记录
static/image/hrline/5.gifstatic/image/hrline/5.gifstatic/image/hrline/5.gif
2.删除重复数据问题
现有一张同学的邮箱表，里面包含学生编号和邮箱两列，现要求删除相同邮箱的记录，且重复的邮箱只保留学生编号最小的那个。
数据准备：
create table Person (
学生编号 int(20),
邮箱 varchar(20));
insert into Person values
(1,'john@example.com'),
(2,'bob@example.com'),
(3,'john@example.com'),
(4,'john@example.com');
https://xxx.ilovefishc.com/album/202012/07/095117qjvdbao6bj876jyj.png
期望效果：
https://xxx.ilovefishc.com/album/202012/07/095122dwlg40d400s8a050.png
delete a
from person a
cross join person b
where a.邮箱 = b.邮箱 and a.学生编号 > b.学生编号
说明：
运用笛卡尔乘积的性质，拿笔画一下就清楚了
static/image/hrline/5.gifstatic/image/hrline/5.gifstatic/image/hrline/5.gif
3.日期比较问题
下面是某店铺每天的营业额，表名为“日销”。“日期”这一列的数据类型是日期类型（date）。现在要求找出比前一天销售额高的记录。比如表中2015-01-02的营业额25万，大于2015-01-01的营业额10万。所以返回2015-01-02这一条记录。
https://xxx.ilovefishc.com/album/202012/07/102059icvsgvaesrzndcva.png
数据准备：
CREATE TABLE 日销 (
Id int(11) DEFAULT NULL,
日期 date DEFAULT NULL,
营业额 int(11) DEFAULT NULL
);
INSERT INTO 日销 VALUES ('1','2015-01-01', '10');
INSERT INTO 日销 VALUES ('2','2015-01-02', '25');
INSERT INTO 日销 VALUES ('3','2015-01-03', '20');
INSERT INTO 日销 VALUES ('4','2015-01-04', '30');
期望效果：
https://xxx.ilovefishc.com/album/202012/07/102059drw7bknnech7zc70.png
select b.*
from 日销 as a
right join 日销 as b
on datediff(b.日期, a.日期)=1
where b.营业额 > a.营业额
说明：
datediff(a,b) = 1 表示a日期-b日期 = 1天
重点知识整理：
时间类函数：
DATE_FORMAT (datetime ,FormatCodes )：转换日期格式
EXTRACT(unit FROM date)：提取日期的指定部分
DAY、week、year：提取date中的日、周、年等
CURRENT_X ：CURRENT_DATE ( ) 当前日期；CURRENT_TIME ( ) 当前时间；CURRENT_TIMESTAMP ( ) ；当前时间戳；NOW ( ) //当前时间。
日期加减：DATEDIFF (date1 ,date2 ) 两个日期差；TIMEDIFF(datetime1,datetime2) : 两个日期时间型相减；DATE_ADD(date2 , INTERVAL d_value d_type )在date2中加上日期或时间；DATE_SUB (date2 , INTERVAL d_value d_type )在date2上减去一个时间；
页:
[1]