当前位置: 首页 > news >正文

SQL 进阶刷题笔记

SQL 进阶刷题笔记

一、MySQL 进阶

这里主要是 MySQL 刷题相关笔记,方便后面温习和查阅,希望可以帮到大家!!!

题1

请计算每张SQL类别试卷发布后,当天5级以上的用户作答的人数uv和平均分avg_score,按人数降序,相同人数的按平均分升序。

描述

在这里插入图片描述

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

问题分解

  1. 获取每张SQL类别试卷发布日期,作为子查询:
    • 筛选试卷类别:WHERE tag = “SQL”
    • 获取试卷ID和发布日期:SELECT exam_id, DATE(release_time)
  2. 筛选发布当天的作答记录:WHERE (exam_id, DATE(start_time)) IN (…)
  3. 筛选5级以上的用户:AND uid IN (SELECT uid FROM user_info WHERE level > 5)
  4. 按试卷ID分组:GROUP BY exam_id
  5. 计算作答人数:count( DISTINCT uid ) AS uv
  6. 计算平均分(保留1位小数):ROUND(avg( score ), 1) AS avg_score

完整代码

SELECTexam_id,count( DISTINCT uid ) AS uv,ROUND(avg( score ), 1) AS avg_score
FROM exam_record 
WHERE (exam_id, DATE(start_time)) IN (SELECT exam_id, DATE(release_time)FROM examination_info WHERE tag = "SQL"
) AND uid IN ( SELECT uid FROM user_info WHERE `level` > 5 )
GROUP BY exam_id
ORDER BY uv DESC, avg_score ASC;

题2

给你一张试卷作答记录表,一张题目练习记录表,请你查询出其中每张试卷和每道题目被作答过的人数和作答的次数,最后按照人数和作答次数的顺序逆序排列

描述

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

问题分解

  1. 先统计试卷区每份试卷被回答的人数和次数:
    • 以试卷exam_id作为分组,便于统计每份试卷被作答的人数和次数。知识点:group by
    • 对于每一组即每一份试卷,统计作答的人数,即uid的数量,要注意去重,即同一人可能回答多次。知识点:count()、distinct
    • 对于每一组即每一份试卷,统计被作答次数,只需要统计出现多少次即可,不用去重。知识点:count()
    • 对查询结果按照先uv再pv的降序排序,order by uv desc, pv desc
  2. 再统计题目区每份试卷被回答的人数和次数:
    • 以试卷question_id作为分组,便于统计每个题目被作答的人数和次数。知识点:group by
    • 对于每一组即每个题目,统计作答的人数,即uid的数量,要注意去重,即同一人可能回答多次。知识点:count()、distinct
    • 对于每一组即每个题目,统计被作答次数,只需要统计出现多少次即可,不用去重。知识点:count()
    • 对查询结果按照先uv再pv的降序排序,order by uv desc, pv desc
  3. 从试卷区的选择中选出全部与从题目区的选择中选出的全部合并,select * from () exam union select * from () practice知识点:union

完整代码

select * from (select exam_id as tid,count(distinct uid) uv,count(*) pvfrom exam_recordgroup by exam_idorder by uv desc ,pv desc
) a
union all
select * from (select question_id as tid,count(distinct uid) uv,count(*) pvfrom practice_recordgroup by question_idorder by uv desc ,pv desc
) b 

题3

请写出一个SQL实现:输出2021年里,所有每次试卷得分都能到85分的人以及至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id和活动号,按用户ID排序输出。

描述

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

问题分解

  1. 筛选2021年每次试卷得分都大于等于85的人和字符串’activity1’:
    • 按照uid进行分组划分,统计每个用户的得分情况。知识点:group by
    • 选出提交时间在2021年的试卷。知识点:select…from…where…、year()
    • 对于每组要求判断最小得分不小于85。知识点:having、min()
  2. 筛选2021年至少有一次用了一半时间就完成高难度试卷且分数大于80的人和字符串’activity2’:
    • 试卷信息和考试信息分布在两个表中,须将其通过exam_id连接起来。知识点:join…on…
    • 从连接后的两个表格中满足四个条件的不重复的用户ID,因为只要求至少一次下述情况(知识点:distinct、where…and…):
      • 提交时间是2021年。year(e_r.submit_time) = 2021
      • 试卷难度是困难。e_i.difficulty = 'hard'
      • 得分大于80。e_r.score > 80
      • 只用了试卷要求时间一半不到的时间就完成。timestampdiff(minute, e_r.start_time, e_r.submit_time) * 2 < e_i.duration
  3. 将两个筛选合并。知识点:union all
  4. 按照用户ID排序输出。知识点:order by uid

完整代码

select uid , 'activity1' as activity
from exam_record 
where year(submit_time) = 2021
group by uid
having min(score) >= 85
union all
select distinct b.uid, 'activity2' as activity
from examination_info a
join exam_record b on a.exam_id = b.exam_id
where year(b.submit_time) = 2021
and a.difficulty = 'hard'
and b.score > 80
and timestampdiff(minute,b.start_time,b.submit_time) * 2 < a.duration
order by uid

题4

找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬,统计他们的2021年试卷完成数和题目练习数; 结果按试卷完成数升序,按题目练习数降序。

描述

在这里插入图片描述

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

问题分解

  1. 找出高难度SQL试卷得分平均值大于80分的7级用户,生成子表t_user_id:
    • 内连接试卷作答表、试卷信息表、用户信息表:exam_record JOIN examination_info USING(exam_id) JOIN user_info USING(uid)
    • 筛选出目标用户:WHERE difficulty=‘hard’ and tag=‘SQL’ and level=7
    • 按用户分组:GROUP BY uid
    • 筛选平均分大于80的分组(用户):HAVING AVG(score)>80
  2. 统计每个用户2021年的试卷完成数,生成子表t_exam_cnt:
    • 筛选作答时间和已完成:WHERE YEAR(submit_time)=2021 AND submit_time IS NOT NULL
    • 按用户分组:GROUP BY uid
    • 统计试卷完成数:count(exam_id) as exam_cnt
  3. 统计每个用户2021年的试题作答数,生成子表t_question_cnt:
    • 筛选作答时间:WHERE YEAR(submit_time)=2021
    • 按用户分组:GROUP BY uid
    • 统计试题作答数:count(question_id) as question_cnt
  4. 关联三张表:
    • 内连接t_user_id和t_exam_cnt,因为用户属性条件必须满足,题目又要求了只输出有试卷完成记录的用户:t_user_id JOIN t_exam_cnt USING(uid)
    • 左连接t_question_cnt:因为只要满足上面条件的结果,即使无试题作答记录也输出:LEFT JOIN t_question_cnt USING(uid)
  5. 特殊处理试题作答数可能为0的情况(此时关联结果中试题作答数为NULL):IFNULL(question_cnt, 0) as question_cnt

完整代码

SELECT uid, exam_cnt, IFNULL(question_cnt, 0) as question_cnt
FROM (SELECT uidFROM exam_recordJOIN examination_info USING(exam_id)JOIN user_info USING(uid)WHERE difficulty='hard' and tag='SQL' and `level`=7GROUP BY uidHAVING AVG(score)>80
) as t_user_id
JOIN (SELECT uid, count(exam_id) as exam_cntFROM exam_recordWHERE YEAR(submit_time)=2021 AND submit_time IS NOT NULLGROUP BY uid
) as t_exam_cnt
USING(uid)
LEFT JOIN (SELECT uid, count(question_id) as question_cntFROM practice_record WHERE YEAR(submit_time)=2021GROUP BY uid
) as t_question_cnt
USING(uid)
ORDER BY exam_cnt, question_cnt desc;

关注林哥,持续更新哦!!!★,°:.☆( ̄▽ ̄)/$:.°★ 。

相关文章:

SQL 进阶刷题笔记

SQL 进阶刷题笔记 一、MySQL 进阶 这里主要是 MySQL 刷题相关笔记&#xff0c;方便后面温习和查阅&#xff0c;希望可以帮到大家&#xff01;&#xff01;&#xff01; 题1 请计算每张SQL类别试卷发布后&#xff0c;当天5级以上的用户作答的人数uv和平均分avg_score&#xff0…...

[网鼎杯 2020 朱雀组]Think Java

SqlDict.java ,其中sql语句处存在sql注入漏洞 package .sqldict;import cn.abc.core.sqldict.Row; import cn.abc.core.sqldict.Table; import java...

AIR32F103(十) 在无系统环境和FreeRTOS环境集成LVGL

目录 AIR32F103(一) 合宙AIR32F103CBT6开发板上手报告AIR32F103(二) Linux环境和LibOpenCM3项目模板AIR32F103(三) Linux环境基于标准外设库的项目模板AIR32F103(四) 27倍频216MHz,CoreMark跑分测试AIR32F103(五) FreeRTOSv202112核心库的集成和示例代码AIR32F103(六) ADC,I2S…...

SpringBoot接口 - 如何统一异常处理

SpringBoot接口如何对异常进行统一封装&#xff0c;并统一返回呢&#xff1f;以上文的参数校验为例&#xff0c;如何优雅的将参数校验的错误信息统一处理并封装返回呢&#xff1f;为什么要优雅的处理异常如果我们不统一的处理异常&#xff0c;经常会在controller层有大量的异常…...

如何使用Python进行数据可视化

数据可视化是一种将数据呈现为图形或图表的技术&#xff0c;它有助于理解和发现数据中的模式和趋势。Python是一种流行的编程语言&#xff0c;有很多库可以帮助我们进行数据可视化。在本文中&#xff0c;我们将介绍使用Python进行数据可视化的基本步骤。 第一步&#xff1a;导…...

vue -- 自定义指令钩子函数补充 自定义过滤器filter参数

自定义指令补充 自定义指令通过钩子函数的形式来实现自定义的功能 这里是几个常用的钩子函数以及它的方法&#xff1a; bind&#xff1a;只调用一次&#xff0c;指令第一次绑定到元素时调用&#xff0c;在这里可以进行一次性的初始化设置。 inserted&#xff1a;被绑定元素插…...

Qt不会操作?Qt原理不知道? | Qt详细讲解

文章目录Qt界面开发必备知识UI界面与控件类型介绍Qt设计器原理控件类型的介绍信号与槽机制处理常用控件创建与设置常见展示型控件创建与设置常见动作型控件创建与设置常见输入型控件创建与设置常见列表控件创建于设置Qt中对象树的介绍项目源码结构刨析.pro.hmain.cpp.cppQt界面…...

LeetCode-面试题 17.05. 字母与数字【前缀和,哈希表】

LeetCode-面试题 17.05. 字母与数字【前缀和&#xff0c;哈希表】题目描述&#xff1a;解题思路一&#xff1a;前缀和。数字为-1&#xff0c;字母为1。我们需要找到的子数组是前缀和之差为0的&#xff0c;例如s[right]-s[left]0&#xff0c;那么s[right]s[left]&#xff0c;变为…...

华为OD机试题 - 叠放书籍(JavaScript)| 机考必刷

更多题库,搜索引擎搜 梦想橡皮擦华为OD 👑👑👑 更多华为OD题库,搜 梦想橡皮擦 华为OD 👑👑👑 更多华为机考题库,搜 梦想橡皮擦华为OD 👑👑👑 华为OD机试题 最近更新的博客使用说明本篇题解:叠放书籍题目输入输出示例一输入输出Code解题思路版权说明华为O…...

【数据库概论】第十一章 数据库并发控制

第十一章 并发控制 在多处理机系统中&#xff0c;每个处理机可以运行一个事务&#xff0c;多个处理机可以同时运行多个事务&#xff0c;实现多个事务并行运行&#xff0c;这就是同时并发方式。当多个用户并发存取数据库时会产生多个事务同时存取同一事务的情况&#xff0c;如果…...

Nginx配置实例-反向代理案例二

实现效果&#xff1a;使用nginx反向代理&#xff0c;根据访问的路径跳转到不同端口服务 nginx监听端口为9000&#xff0c; 访问 http://127.0.0.1:9000/edu/ 直接跳转到127.0.0.1:8080 访问 http://127.0.0.1:9000/vod/ 直接跳转到127.0.0.1:8081 一、准备工作 1. 准备两个tom…...

HTML 字符集

为了正确显示 HTML 页面&#xff0c;Web 浏览器必须知道要使用哪个字符集。 从 ASCII 到 UTF-8 ASCII 是第一个字符编码标准。ASCII 定义了 128 种可以在互联网上使用的字符&#xff1a;数字&#xff08;0-9&#xff09;、英文字母&#xff08;A-Z&#xff09;和一些特殊字符…...

【C语言】每日刷题 —— 牛客语法篇(3)

前言 大家好&#xff0c;继续更新专栏c_牛客&#xff0c;不出意外的话每天更新十道题&#xff0c;难度也是从易到难&#xff0c;自己复习的同时也希望能帮助到大家&#xff0c;题目答案会根据我所学到的知识提供最优解。 &#x1f3e1;个人主页&#xff1a;悲伤的猪大肠9的博客…...

基于Vue3和element-plus实现一个完整的登录功能

先看一下最终要实现的效果:登录页面:注册页面:(1)引入element-plus组件库引入组件库的方式有好多种,在这里我就在main.js全局引入了.npm i element-plus -Smain.js中代码:import { createApp } from "vue"; //element-plus import ElementPlus from "element-pl…...

【java】Java 中泛型的实现原理

文章目录前序1. 泛型1.1 泛型方法1.2 泛型类1.3 泛型接口2. 泛型的基本原理3. 小结前序 泛型是 Java 开发中常用的技术&#xff0c;了解泛型的几种形式和实现泛型的基本原理&#xff0c;有助于写出更优质的代码。本文总结了 Java 泛型的三种形式以及泛型实现原理。 1. 泛型 …...

【C++提高编程】C++全栈体系(二十七)

C提高编程 第五章 STL- 常用算法 三、常用排序算法 算法简介&#xff1a; sort //对容器内元素进行排序random_shuffle //洗牌 指定范围内的元素随机调整次序merge // 容器元素合并&#xff0c;并存储到另一容器中reverse // 反转指定范围的元素 1. sort 功能描述&#…...

软考高级信息系统项目管理师系列之三十九:项目集管理

软考高级信息系统项目管理师系列之三十九:项目集管理 一、项目集管理内容二、项目集管理基础概述1.项目集定义2.项目集活动3.项目集管理三、项目集的管理过程四、项目集治理1.项目集治理概述2.项目集指导委员会的职责3.项目集治理功能五、项目集生命周期1.项目集生命周期三个阶…...

44-Golang中的channel

Golang中的channel为什么要使用channelchannel的介绍channel的基本使用定义/声明channel管道的遍历和关闭channel的关闭channel的遍历goroutine和channel结合应用实例1应用实例2案例注意事项为什么要使用channel 前面使用全局变量加锁同步来解决goroutine的通讯&#xff0c;但…...

80/20法则

80/20法则&#xff08;The 80/20 Rule&#xff09;又称为帕累托法则(Pareto Principle&#xff09;、二八定律、帕累托定律、最省力法则、不平衡原则、犹太法则、马特莱法则等一、什么是80/20法则80/20法则&#xff08;The 80/20 Rule&#xff09;&#xff0c;又称为帕累托法则…...

计算机网络高频面试题(四)

一、什么是计算机网络 是一个将分散的、具有独立功能的计算机系统&#xff0c;通过通信设备与线路连接起来&#xff0c;由功能完善的软件实现资源共享和信息传递的系统 按分布范围&#xff0c;计算机网络里有局域网LAN和广域网WAN, 其中局域网的代表以太网&#xff0c;以及这…...

IDEA运行Tomcat出现乱码问题解决汇总

最近正值期末周&#xff0c;有很多同学在写期末Java web作业时&#xff0c;运行tomcat出现乱码问题&#xff0c;经过多次解决与研究&#xff0c;我做了如下整理&#xff1a; 原因&#xff1a; IDEA本身编码与tomcat的编码与Windows编码不同导致&#xff0c;Windows 系统控制台…...

XCTF-web-easyupload

试了试php&#xff0c;php7&#xff0c;pht&#xff0c;phtml等&#xff0c;都没有用 尝试.user.ini 抓包修改将.user.ini修改为jpg图片 在上传一个123.jpg 用蚁剑连接&#xff0c;得到flag...

Linux链表操作全解析

Linux C语言链表深度解析与实战技巧 一、链表基础概念与内核链表优势1.1 为什么使用链表&#xff1f;1.2 Linux 内核链表与用户态链表的区别 二、内核链表结构与宏解析常用宏/函数 三、内核链表的优点四、用户态链表示例五、双向循环链表在内核中的实现优势5.1 插入效率5.2 安全…...

以下是对华为 HarmonyOS NETX 5属性动画(ArkTS)文档的结构化整理,通过层级标题、表格和代码块提升可读性:

一、属性动画概述NETX 作用&#xff1a;实现组件通用属性的渐变过渡效果&#xff0c;提升用户体验。支持属性&#xff1a;width、height、backgroundColor、opacity、scale、rotate、translate等。注意事项&#xff1a; 布局类属性&#xff08;如宽高&#xff09;变化时&#…...

《从零掌握MIPI CSI-2: 协议精解与FPGA摄像头开发实战》-- CSI-2 协议详细解析 (一)

CSI-2 协议详细解析 (一&#xff09; 1. CSI-2层定义&#xff08;CSI-2 Layer Definitions&#xff09; 分层结构 &#xff1a;CSI-2协议分为6层&#xff1a; 物理层&#xff08;PHY Layer&#xff09; &#xff1a; 定义电气特性、时钟机制和传输介质&#xff08;导线&#…...

渲染学进阶内容——模型

最近在写模组的时候发现渲染器里面离不开模型的定义,在渲染的第二篇文章中简单的讲解了一下关于模型部分的内容,其实不管是方块还是方块实体,都离不开模型的内容 🧱 一、CubeListBuilder 功能解析 CubeListBuilder 是 Minecraft Java 版模型系统的核心构建器,用于动态创…...

镜像里切换为普通用户

如果你登录远程虚拟机默认就是 root 用户&#xff0c;但你不希望用 root 权限运行 ns-3&#xff08;这是对的&#xff0c;ns3 工具会拒绝 root&#xff09;&#xff0c;你可以按以下方法创建一个 非 root 用户账号 并切换到它运行 ns-3。 一次性解决方案&#xff1a;创建非 roo…...

【无标题】路径问题的革命性重构:基于二维拓扑收缩色动力学模型的零点隧穿理论

路径问题的革命性重构&#xff1a;基于二维拓扑收缩色动力学模型的零点隧穿理论 一、传统路径模型的根本缺陷 在经典正方形路径问题中&#xff08;图1&#xff09;&#xff1a; mermaid graph LR A((A)) --- B((B)) B --- C((C)) C --- D((D)) D --- A A -.- C[无直接路径] B -…...

【JVM】Java虚拟机(二)——垃圾回收

目录 一、如何判断对象可以回收 &#xff08;一&#xff09;引用计数法 &#xff08;二&#xff09;可达性分析算法 二、垃圾回收算法 &#xff08;一&#xff09;标记清除 &#xff08;二&#xff09;标记整理 &#xff08;三&#xff09;复制 &#xff08;四&#xff…...

MinIO Docker 部署:仅开放一个端口

MinIO Docker 部署:仅开放一个端口 在实际的服务器部署中,出于安全和管理的考虑,我们可能只能开放一个端口。MinIO 是一个高性能的对象存储服务,支持 Docker 部署,但默认情况下它需要两个端口:一个是 API 端口(用于存储和访问数据),另一个是控制台端口(用于管理界面…...