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

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]
查看完整版本: sql练习及理解_3