当前位置: 首页 > 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;以及这…...

从WWDC看苹果产品发展的规律

WWDC 是苹果公司一年一度面向全球开发者的盛会&#xff0c;其主题演讲展现了苹果在产品设计、技术路线、用户体验和生态系统构建上的核心理念与演进脉络。我们借助 ChatGPT Deep Research 工具&#xff0c;对过去十年 WWDC 主题演讲内容进行了系统化分析&#xff0c;形成了这份…...

在rocky linux 9.5上在线安装 docker

前面是指南&#xff0c;后面是日志 sudo dnf config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo sudo dnf install docker-ce docker-ce-cli containerd.io -y docker version sudo systemctl start docker sudo systemctl status docker …...

【AI学习】三、AI算法中的向量

在人工智能&#xff08;AI&#xff09;算法中&#xff0c;向量&#xff08;Vector&#xff09;是一种将现实世界中的数据&#xff08;如图像、文本、音频等&#xff09;转化为计算机可处理的数值型特征表示的工具。它是连接人类认知&#xff08;如语义、视觉特征&#xff09;与…...

鸿蒙中用HarmonyOS SDK应用服务 HarmonyOS5开发一个生活电费的缴纳和查询小程序

一、项目初始化与配置 1. 创建项目 ohpm init harmony/utility-payment-app 2. 配置权限 // module.json5 {"requestPermissions": [{"name": "ohos.permission.INTERNET"},{"name": "ohos.permission.GET_NETWORK_INFO"…...

鸿蒙DevEco Studio HarmonyOS 5跑酷小游戏实现指南

1. 项目概述 本跑酷小游戏基于鸿蒙HarmonyOS 5开发&#xff0c;使用DevEco Studio作为开发工具&#xff0c;采用Java语言实现&#xff0c;包含角色控制、障碍物生成和分数计算系统。 2. 项目结构 /src/main/java/com/example/runner/├── MainAbilitySlice.java // 主界…...

华硕a豆14 Air香氛版,美学与科技的馨香融合

在快节奏的现代生活中&#xff0c;我们渴望一个能激发创想、愉悦感官的工作与生活伙伴&#xff0c;它不仅是冰冷的科技工具&#xff0c;更能触动我们内心深处的细腻情感。正是在这样的期许下&#xff0c;华硕a豆14 Air香氛版翩然而至&#xff0c;它以一种前所未有的方式&#x…...

LabVIEW双光子成像系统技术

双光子成像技术的核心特性 双光子成像通过双低能量光子协同激发机制&#xff0c;展现出显著的技术优势&#xff1a; 深层组织穿透能力&#xff1a;适用于活体组织深度成像 高分辨率观测性能&#xff1a;满足微观结构的精细研究需求 低光毒性特点&#xff1a;减少对样本的损伤…...

Kubernetes 网络模型深度解析:Pod IP 与 Service 的负载均衡机制,Service到底是什么?

Pod IP 的本质与特性 Pod IP 的定位 纯端点地址&#xff1a;Pod IP 是分配给 Pod 网络命名空间的真实 IP 地址&#xff08;如 10.244.1.2&#xff09;无特殊名称&#xff1a;在 Kubernetes 中&#xff0c;它通常被称为 “Pod IP” 或 “容器 IP”生命周期&#xff1a;与 Pod …...

SpringAI实战:ChatModel智能对话全解

一、引言&#xff1a;Spring AI 与 Chat Model 的核心价值 &#x1f680; 在 Java 生态中集成大模型能力&#xff0c;Spring AI 提供了高效的解决方案 &#x1f916;。其中 Chat Model 作为核心交互组件&#xff0c;通过标准化接口简化了与大语言模型&#xff08;LLM&#xff0…...

2.2.2 ASPICE的需求分析

ASPICE的需求分析是汽车软件开发过程中至关重要的一环&#xff0c;它涉及到对需求进行详细分析、验证和确认&#xff0c;以确保软件产品能够满足客户和用户的需求。在ASPICE中&#xff0c;需求分析的关键步骤包括&#xff1a; 需求细化&#xff1a;将从需求收集阶段获得的高层需…...