鱼C论坛

 找回密码
 立即注册
查看: 2009|回复: 0

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

[复制链接]
发表于 2020-12-7 22:27:28 | 显示全部楼层 |阅读模式

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

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

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


                               
登录/注册后可看大图

                               
登录/注册后可看大图


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


                               
登录/注册后可看大图

                               
登录/注册后可看大图


1. 分组比较类问题
有一张学生成绩表,其中包含学生编号,课程编号,成绩;还有一张课程表,其中包含课程编号,课程名称。求每门课程编号,课程名称以及每门课程最高的成绩。

数据准备:
  1. create table 课程(
  2.     课程编号 varchar(10),
  3.     课程名称 nvarchar(10),
  4.     教师编号 varchar(10));

  5. insert into 课程 values('01' , '语文' , '02');
  6. insert into 课程 values('02' , '数学' , '01');
  7. insert into 课程 values('03' , '英语' , '03');

  8. create table `成绩`
  9. (`学生编号` varchar(10),
  10. `课程编号` varchar(10),
  11. `成绩` decimal(18,1));

  12. insert into `成绩` values('01' , '01' , 80);
  13. insert into `成绩` values('01' , '02' , 90);
  14. insert into `成绩` values('01' , '03' , 99);
  15. insert into `成绩` values('02' , '01' , 70);
  16. insert into `成绩` values('02' , '02' , 60);
  17. insert into `成绩` values('02' , '03' , 80);
  18. insert into `成绩` values('03' , '01' , 80);
  19. insert into `成绩` values('03' , '02' , 80);
  20. insert into `成绩` values('03' , '03' , 80);
  21. insert into `成绩` values('04' , '01' , 50);
  22. insert into `成绩` values('04' , '02' , 30);
  23. insert into `成绩` values('04' , '03' , 20);
  24. insert into `成绩` values('05' , '01' , 76);
  25. insert into `成绩` values('05' , '02' , 87);
  26. insert into `成绩` values('06' , '01' , 31);
  27. insert into `成绩` values('06' , '03' , 34);
  28. insert into `成绩` values('07' , '02' , 89);
  29. insert into `成绩` values('07' , '03' , 98);
复制代码



                               
登录/注册后可看大图


                               
登录/注册后可看大图



期望效果:

                               
登录/注册后可看大图


  1. select distinct a.课程编号, a.课程名称, b.成绩
  2. from 课程 as a join 成绩 as b
  3. on a.课程编号 = b.课程编号
  4. where (b.课程编号, b.成绩) in (
  5.         SELECT 课程编号, max(成绩)
  6.         from 成绩 GROUP BY 课程编号);
复制代码


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


                               
登录/注册后可看大图

                               
登录/注册后可看大图

                               
登录/注册后可看大图


2.删除重复数据问题
现有一张同学的邮箱表,里面包含学生编号和邮箱两列,现要求删除相同邮箱的记录,且重复的邮箱只保留学生编号最小的那个。

数据准备:
  1. create table Person (
  2. 学生编号 int(20),
  3. 邮箱 varchar(20));

  4. insert into Person values
  5. (1,'john@example.com'),
  6. (2,'bob@example.com'),
  7. (3,'john@example.com'),
  8. (4,'john@example.com');
复制代码


                               
登录/注册后可看大图


期望效果:

                               
登录/注册后可看大图


  1. delete a
  2. from person a
  3. cross join person b
  4. where a.邮箱 = b.邮箱 and a.学生编号 > b.学生编号
复制代码


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



                               
登录/注册后可看大图

                               
登录/注册后可看大图

                               
登录/注册后可看大图



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


                               
登录/注册后可看大图


数据准备:
  1. CREATE TABLE 日销 (
  2. Id int(11) DEFAULT NULL,
  3. 日期 date DEFAULT NULL,
  4. 营业额 int(11) DEFAULT NULL
  5. );

  6. INSERT INTO 日销 VALUES ('1','2015-01-01', '10');
  7. INSERT INTO 日销 VALUES ('2','2015-01-02', '25');
  8. INSERT INTO 日销 VALUES ('3','2015-01-03', '20');
  9. INSERT INTO 日销 VALUES ('4','2015-01-04', '30');
复制代码


期望效果:

                               
登录/注册后可看大图


  1. select b.*  
  2. from 日销 as a
  3. right join 日销 as b
  4. on datediff(b.日期, a.日期)=1
  5. 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上减去一个时间;


想知道小甲鱼最近在做啥?请访问 -> ilovefishc.com
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

小黑屋|手机版|Archiver|鱼C工作室 ( 粤ICP备18085999号-1 | 粤公网安备 44051102000585号)

GMT+8, 2024-3-29 00:26

Powered by Discuz! X3.4

© 2001-2023 Discuz! Team.

快速回复 返回顶部 返回列表