sql练习及理解_3

junkaiw · 发表于 2020-12-7 22:27:28

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

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

x

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

登录/注册后可看大图

目录
1. 分组比较类问题
2.删除重复数据问题
3.日期比较问题

登录/注册后可看大图

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

登录/注册后可看大图

期望效果：

登录/注册后可看大图

select distinct a.课程编号, a.课程名称, b.成绩
from 课程 as a join 成绩 as b 
on a.课程编号 = b.课程编号 
where (b.课程编号, b.成绩) in (
        SELECT 课程编号, max(成绩)
        from 成绩 GROUP BY 课程编号);

说明：
使用distinct用于去重，避免一门课出现多个最高分记录

登录/注册后可看大图

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

登录/注册后可看大图

期望效果：

登录/注册后可看大图

delete a 
from person a 
cross join person b
where a.邮箱 = b.邮箱 and a.学生编号 > b.学生编号

说明：
运用笛卡尔乘积的性质，拿笔画一下就清楚了

登录/注册后可看大图

3.日期比较问题
下面是某店铺每天的营业额，表名为“日销”。“日期”这一列的数据类型是日期类型（date）。现在要求找出比前一天销售额高的记录。比如表中2015-01-02的营业额25万，大于2015-01-01的营业额10万。所以返回2015-01-02这一条记录。

登录/注册后可看大图

数据准备：

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

期望效果：

登录/注册后可看大图

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上减去一个时间；

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

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

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