MYSQL学习笔记（2）

已于 2022-03-08 19:17:07 修改
阅读量493 收藏
点赞数
文章标签： mysql 学习 数据库
于 2022-03-05 09:00:03 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/A133333/article/details/123096710
版权
这篇博客详细记录了在牛客网上进行的多表查询题目复盘，涉及月均完成试卷数、作答类别、试卷发布当天情况、得分超过80的用户等级分布等SQL问题的分析和解答，讲解了不同查询技巧和需要注意的细节。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

题目复盘——多表查询

题源牛客网——记录一下自己的学习

SQL20 月均完成试卷数不小于3的用户爱作答的类别

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

问题分析

（1）月均完成试卷数————总完成次数/完成时所在月份数量（有争议）>=3；
（2）完成时所在月份数————count(distinct date_format(submit_time,’%Y%m’));
（3）总完成次数————count(submit_time)；
（4）作答次数————count(start_time)；
（5）爱作答类别————group by tag；left join examination_info；
（6）排序————order by；

答案重写

SELECT tag,COUNT(start_time) tag_cnt FROM exam_record e_r
LEFT JOIN examination_info e_i ON e_r.exam_id=e_i.exam_id
WHERE uid in(
SELECT uid FROM exam_record GROUP BY uid
HAVING COUNT(submit_time)/COUNT(DISTINCT DATE_FORMAT(submit_time,'%Y%m'))>=3)
GROUP BY tag
ORDER BY tag_cnt DESC;

细节问题

这个题对于其中条件的解读有点争议，尤其是那个月均，还有当月之类的含糊词语，容易理解错。

SQL21 试卷发布当天作答人数和平均分

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

问题分析

（1）5级以上用户————where level >5；
（2）试卷发放当天作答人数————count(distinct uid);date_format(release_time,’%Y%m%d’)=date_format(submit_time,’%Y%m%d’)；
（3）平均分————sum()/count();avg()；
（4）保留一位小数————round(,1)；

答案重写

SELECT exam_id,count(distinct e_r.uid) uv,ROUND(avg(score),1) avg_score FROM exam_record e_r 
LEFT JOIN user_info u_i ON u_i.uid=e_r.uid
LEFT JOIN examination_info e_i ON e_i.exam_id=e_r.exam_id
WHERE level>5 AND tag='SQL' AND DATE_FORMAT(e_r.release_time,'%Y%m%d')=DATE_FORMAT(submit_time,'%Y%m%d')
GROUP BY exam_id
ORDER BY uv DESC,avg_score ASC;

SQL22 作答试卷得分大于80的人的用户等级分布

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

问题分析

（1）SQL类别试卷得分大于80———tag=‘SQL’,score>80；
（2）等级分布————group by level；
（3）每个等级人数————count(distinct uid)；
（4）数量降序————order by level_cnt desc；

答案重写

（1）多表联结法

SELECT level,COUNT(distinct e_r.uid) level_cnt FROM exam_record e_r
JOIN examination_info e_i ON e_i.exam_id=e_r.exam_id
JOIN user_info u_i ON u_i.uid=e_r.uid
WHERE score>80 AND tag='SQL'
GROUP BY level
ORDER BY level_cnt DESC,level DESC;

（2）嵌套子查询法

SELECT level,count(distinct uid) level_cnt 
FROM user_info u_i
WHERE uid in(
SELECT uid FROM exam_record WHERE exam_id in
(SELECT exam_id FROM examination_info WHERE tag='SQL') 
AND score>80)
GROUP BY level
ORDER BY level_cnt DESC,level DESC;

相比连接查询使用in运算符思路更清晰
1.找出SQL试卷的exam_id
2.根据exam_id找到做了SQL试卷且得分大于80的uid
3.根据uid找到对应的用户，使用相关聚合函数完成查询
牛客92009084号

细节问题

一定要弄清楚每个条件的具体定义究竟是什么。

SQL23 每个题目和每份试卷被作答的人数和次数

在这里插入图片描述
SQL23 每个题目和每份试卷被作答的人数和次数
在这里插入图片描述

问题分析

（1）试卷和题目分别位于两张表，考虑分别查询出符合条件的结果用UNION ALL连结起来，UNION ALL不会去除重复项；
（2）被作答人数————COUNT(DISTINCT uid)
（3）被作答次数————COUNT(start_time)
（4）GROUP BY
（5）降序————ORDER BY DESC

答案重写

SELECT exam_id tid,COUNT(DISTINCT uid) uv,COUNT(start_time) pv
FROM exam_record e_r
GROUP BY exam_id
UNION ALL
select question_id tid,COUNT(DISTINCT uid) uv,COUNT(submit_time) pv
FROM practice_record
GROUP BY question_id
ORDER BY LEFT(tid,1) desc,UV DESC,PV DESC;

细节问题

（1）union可以使用任何selcet语句，但order by子句只能在最后一次使用。

SQL24 分别满足两个活动的人

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

问题分析

（1）注意到示例里面，同时满足两个活动的用户作为两条记录，而不是在activity列一条记录里显示两个活动，这就降低了难度，可以分别查询出符合活动的用户，再用UNION ALL即可；
（2）activity1每次试卷得分都能到85————score>=85
（3）activity2至少有一次用了一半时间就完成高难度试卷且分数大于80————TIMESTAMPDIFF(second,start_time,submit_time)<=duration*60/2 and score>80；LEFT JOIN e_i ON …difficulty=‘hard’；
（4）按用户id排序输出————order by uid;

答案重写

select uid,'activity1' activity
FROM exam_record e_r
WHERE YEAR(submit_time)=2021
AND uid NOT IN(SELECT uid FROM exam_record WHERE score<85)
GROUP BY uid
UNION ALL
SELECT uid,'activity2' activity FROM exam_record e_r
LEFT JOIN examination_info e_i ON e_i.exam_id=e_r.exam_id
WHERE difficulty='hard' 
AND TIMESTAMPDIFF(second,start_time,submit_time)<=duration*60/2 
AND score>80 
AND YEAR(submit_time)=2021
GROUP BY uid
ORDER BY uid,RIGHT(activity,1);

细节问题

（1）填充文本为新的一列时，要加单引号‘activity1’不然会报错找不到这一列；
（2）TIMESTAMPDIFF后面可以用天(day)、小时(hour)、分钟(minute)、秒(second)，datediff函数，返回值是相差的天数，不能定位到小时、分钟和秒；
（3）本题一定要用秒作为计算时间差的单位时长，首先所给时间都是到秒的，而且小于等于一般的时间意味着超过一秒都不算，如果单位是分钟就会筛出答题时长超过几秒但不足一分钟的用户，这样是不对的；
（4）不要忘记年份限制；

SQL25 满足条件的用户的试卷完成数和题目练习数

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

问题分析

（1）高难度SQL试卷得分平均值大于80——tag=‘SQL’ and difficulty=‘hard’；
（2）七级用户————left join user_info on…;level=7；
（3）请注意以上条件只是对用户也就是uid的限制，而要统计次数的试卷和题目是没有以上限制条件的！包括所有类型和难度！
（4）试卷总完成次数——count(distinct if(e_r.score is not null,e_r.exam_id,null)
（5）题目总练习次数——
（6）2021年————year()=2021；
（7）需要left join 两表，可以用子查询筛选uid；
（8）排序————ORDER BY；

答案重写

select n_e_r.uid uid,exam_cnt,if(n_e_r.uid=n_p_r.uid,question_cnt,0) question_cnt 
from (select e_r.uid uid,count(distinct if(e_r.score is not null,e_r.exam_id,null)) exam_cnt
from exam_record e_r
where e_r.uid in 
      (select e_r.uid from exam_record e_r left join user_info u_i on u_i.uid=e_r.uid
       left join examination_info e_i on e_i.exam_id=e_r.exam_id
       where level>=7 and difficulty='hard' and tag='SQL' and year(e_r.submit_time)=2021
       GROUP BY e_r.uid 
       HAVING avg(e_r.score)>80)
GROUP BY e_r.uid) n_e_r
left JOIN (select p_r.uid uid,count(distinct if(score is not null,submit_time,null)) question_cnt
from practice_record p_r where year(p_r.submit_time)=2021
group by p_r.uid) n_p_r
ON n_p_r.uid=n_e_r.uid
order by exam_cnt ASC,question_cnt DESC;

细节问题

1、为什么要用left join？ 因为一个人可能做了试卷 exam，却没有做习题practice，如果 join exam_record和
practice_record on uid，那么由于这个人的 uid 在 practice_record
中不存在，uid就会被排除，不符合题意。
2、为什么明明题目问的是总完成次数，却要 count distinct？ 因为 left join
导致出现了重复行，也就是笛卡尔积，所以，本题目的distinct不是对 exam_id或者
question_id去重，而是为了解决多出来的重复行的问题。
id：大力不失眠

关于所取数据的定义评论区有很多讨论，感觉题目设置数据也有点问题，这个id的评论再仔细看一下。

SQL26 每个6/7级用户活跃情况

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

问题分析

（1）总活跃月份和活跃天数是包含两个表的活跃度，但是！如果分别count再加起来就错了！因为要考虑到做题和做试卷可能在同一天，这样算出来次数会多！
（2）可以用UNION ALL，从两个表中select出字段重命名并预处理一下，将两个表合并，再从合并后的大表中select所需字段。
（3）总活跃月份数没有年份限制，而其余三者有年份限制，那么此时就不能在where后用，可以用case when或者if。

答案重写

select user_info.uid uid,
        count(distinct act_month) as act_month_total,
        count(distinct case when year(act_time)='2021'then act_day end) as act_days_2021,
        count(distinct case when year(act_time)='2021' and tag='exam' then act_day end) as act_days_2021_exam,
        count(distinct case when year(act_time)='2021' and tag='question'then act_day end) as act_days_2021_question
from (SELECT uid,exam_id as ans_id,start_time as act_time,date_format(start_time,'%Y%m') as act_month, date_format(start_time,'%Y%m%d') as act_day,'exam' as tag from exam_record
     UNION ALL
     select uid,question_id as ans_id,submit_time as act_time,date_format(submit_time,'%Y%m') as act_month, date_format(submit_time,'%Y%m%d') as act_day,'question' as tag from practice_record ) total 
     right join user_info on total.uid=user_info.uid
where user_info.level in (6,7)
group by user_info.uid
order by act_month_total desc,act_days_2021 desc

如果用if部分：

SELECT ui.uid,
COUNT(DISTINCT DATE_FORMAT(act_time,'%Y%m')) as act_month_total,
COUNT(DISTINCT IF(YEAR(act_time)=2021,DATE(act_time),null)) as act_days_2021,
COUNT(DISTINCT IF(YEAR(act_time)=2021 and tag='exam',act_time,null)) as act_days_2021_exam,
COUNT(DISTINCT IF(YEAR(act_time)=2021 and tag='question',act_time,null)) as act_days_2021_question

细节问题

（1）then后面的字段，只能直接获得，不能通过间接获得，比如上面的一个相加的值，要想那些写，只能在包一层，才能获得使用。
（2）一上来直接踩两个相加的坑了

不响丸辣
关注
MySQL中distinct的使用方法
lmy86263的博客
06-22 6万+
本文的环境是Windows 10，MySQL版本是5.7.12-log 一、基本使用distinct一般是用来去除查询结果中的重复记录的，而且这个语句在select、insert、delete和update中只可以在select中使用，具体的语法如下：select distinct expression[,expression...] from tables [where conditions]
Mysql--limit、distinct、ifnull用法
weixin_45638528的博客
04-08 1122
适用场景：当要显示的数据一页显示不全，需要分页显示时；或只显示部分数据时 语法： select 查询列表 from 表1 (inner\left\right) join 表2 on 连接条件 where 筛选条件 group by 分组字段 having 分组后的筛选条件 order by 排序的字段 asc\desc limit offset,size; 注意： offse
mysql count if distinct
热门推荐
johnhuster的专栏
04-12 1万+
mysql count if distinct
MySQL中一些常见及特殊函数用法
相见恨晚
06-25 1279
1.decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值) 该函数的含义如下：IF 条件=值1 THEN　　　　RETURN(翻译值1)ELSIF 条件=值2 THEN　　　　RETURN(翻译值2)　　　　......ELSIF 条件=值n THEN　　　　RETURN(翻译值n)ELSE　　　　RETURN(缺省值)END IF 例子：(SUBSTRB(P....
mysql count 实现复杂统计解析
hhhzua的专栏
07-29 1201
mysql的count函数可以计算符合条件的记录条数，比如： select count(*) from users; 执行结果： 上面的sql只是将查询到的记录总数输出，count函数本身还可以配合if函数实现更复杂的计数： select count(if(status = 1, 1, null)) from users 注意，count会将所有非null值计数，所以if里面不符合条件应该返回null。 如果需要按某个字段计算去重后的数量，则需使用 distinct 关键字： select count
mysql 获得年月日的数值_mysql函数date_format统计刷选按年月日统计的数据
weixin_31363913的博客
01-20 147
/*原型*/SELECTcount(did) AS sum,date_format(releasetime, '%Y-%m-%d') AS releasetimeFROM hengtu_demandWHERE 1GROUP BYdate_format(releasetime, '%Y-%m-%d')/*按年分组 @参数 需求id sid=1*/SELECTb.sid,b.`name` as sn...
MySQL学习笔记2：数据库的基本操作(创建删除查看)
12-15
在MySQL学习过程中，掌握数据库的基本操作至关重要，主要包括创建、删除和查看数据库。 **创建数据库** 创建数据库的命令是`CREATE DATABASE`，例如： ```sql mysql> CREATE DATABASE school; ``` 在这个例子中，`...
MySQL学习笔记 MySQL学习笔记
09-28
MySQL是世界上最受欢迎的关系型数据库管理系统之一，尤其在Web应用...学习MySQL不仅涉及理论知识，还需要大量的实践操作来熟悉其语法和功能。掌握MySQL能让你在数据管理、Web开发和系统集成等领域具备更强的竞争力。
MySQL学习笔记.zip
最新发布
02-23
这份“MySQL学习笔记”将引导我们深入理解其核心概念和实用技能。 一、MySQL简介 MySQL是一个开源、免费的数据库系统，由瑞典的MySQL AB公司开发，后被Oracle公司收购。它的设计目标是速度、可移植性和简洁性，支持...
数据库 MySQL 学习笔记高级篇.md
09-01
数据库 MySQL 学习笔记高级篇.md
MySQL学习笔记
12-19
### MySQL学习笔记知识点详解 #### 一、数据库概述 数据库是指按照特定的数据结构来组织、存储和管理数据的集合体。随着信息技术的进步和社会需求的增长，数据库已经从最初的简单存储发展成为用户所需的各种复杂...
date_format ：格式化日期 日期函数
m0_64303046的博客
08-28 2381
date_format ：格式化日期 日期函数 //月活跃天数除以人数 等于平均活跃天数 日期函数
牛客网【SQL】经典难题
qq_65184431的博客
08-04 814
sql难题
distinct if count搭配使用
mmdsl083的博客
11-30 1463
根据某列状态统计数量，并且去重 DISTINCT是为了演示联表的时候重复数据情况，单表的话是不用DISTINCT的。
Tidb 跨分区使用count distinct if 统计不准（有bug）
lvqinglou的博客
09-15 792
我们公司使用了tidb，然后我就发现了这东西有点不靠谱，用新技术果然要付出代价。不要问我为什么用，反正很蛋疼。
jpa mysql去重复查询_JPA 查询Distinct多对一示例
weixin_32009069的博客
01-29 2801
JPA教程 - JPA查询Distinct多对一示例DISTINCT运算符在功能上等同于同名的SQL运算符。收集结果集后，将删除重复值，以便只返回唯一结果。以下JPQL在多对一映射中使用DISTINCT运算符。例子下面的代码来自Department.java。package cn.w3cschool.common;import java.util.ArrayList;import java.ut...
mysql 查询帖子 用户_我要用sql查询出来，我所发布的帖子和我关注的用户发布的帖子，这个sql该怎么写啊？...
weixin_34496367的博客
01-27 444
selectt.*fromtalktwheret.user_id=2Unionselectt.*fromtalkt,user_contactcwherec.user_id=2andc.contact_type=1andc.be_user_id=t.user_id这么写可以但是不想这么写 ，想用 内连接或者左连接存在一种情况就是这个user在user_co...
Mysql高级语句（一）
HB199753的博客
10-30 1367
一、按关键字排序 1、语法结构 排序的字段可以根据具体需求进行选择，没有限制。排序的关键字可以使用 ASC 或者DESC。ASC 是按照升序进行排序的， 是默认的排序方式，即 ASC 可以省略。SELECT 语句中如果没有指定具体的排序方式，则默认按 ASC 方式进行排序。 DESC 是按降序方式进行排列。 select colum1，column2，。。。from table_name order by column1 desc|asc ,column2 desc|asc........; 2、
nodejs之mysql事务封装与使用
wushichao0325的博客
11-10 6253
nodejs之mysql的事务封装简介所需模块文件结构干货部分1 此部分为mysql初始化配置信息：2 数据库连接3 orderModel的封装和对数据库的使用4 model使用到这里我们就结束了，如果你喜欢，那谢谢你的浏览，如果不喜欢，那请留下你的建议。 简介 本文章主要针对nodejs中MySQL模块下事务的封装，利用es6新特性中的class类的封装，让事务更加容易调用和使用。（主要因为网上...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值