SQL进阶day10————多表查询

1嵌套子查询

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

我的代码：思路就是这么个思路，反正没有搞出来当月均完成试卷数

select tag,count(submit_time) tag_cnt
from exam_record er join examination_info ei
on er.exam_id = ei.exam_id
where uid in (当月均完成试卷数>=3)
group by tag
order by tag_cnt desc

反正没有搞出来当月均完成试卷数，报错： 

大佬正确答案：

居然和我的差不多，我就分组的时候少了uid，还有按照uid进行分组。此外，作答次数=count(start_time)，而不是提交次数。

select tag, count(start_time) as tag_cnt
from exam_record er inner join examination_info ei
on er.exam_id = ei.exam_id
where uid in 
(select uid
from exam_record er 
group by uid, month(start_time)
having count(submit_time) >= 3)
group by tag
order by tag_cnt desc

复盘：

（1）uid,month(submit_time)是啥呢，如果原来只是按照month(submit_time)进行分组，1002,1003,1005都有多个

 （2）如果按照uid,month(submit_time)进行分组，情况如下

（3）这么如果只是按照month(submit_time) 分组，uid,month(submit_time)只有9和null两种情况，当使用GROUP BY子句时，NULL值将被视为一个独立的分组，并在结果集中显示一个额外的分组来表示它。

（4）结果显示只有1002,1005这两个用户满足要求，然后查找这两个用户的作答的类别及作答次数。

 

（5）验证：where uid =1002 or uid = 1005  等价于 子查询的效果

 

 

还有一种大佬做法是：

select tag,count(start_time) tag_cnt
from exam_record er join examination_info ei
on er.exam_id = ei.exam_id
-- where uid =1002 or uid = 1005 
WHERE er.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

 这样出来的两个用户也是1002和1005：

  • 相当于：月均完成试卷数 = 总完成次数/哪些月份提交了数据

COUNT(DISTINCT DATE_FORMAT(submit_time, "%Y%m"))=1，所以答案一样的。

COUNT(DISTINCT DATE_FORMAT(submit_time, "%Y%m"))中的distinct很重要：

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

我的代码：答案错误，但是我能发现的的改了，

（1）SQL类，（2）当天，（3）作答人数

select er.exam_id,
any_value(count(er.submit_time)) uv,
round(avg(er.score),1) avg_score
from examination_info ei join exam_record er
on ei.exam_id = er.exam_id
where ei.tag = "SQL" 
and day(submit_time)=day(release_time)
and er.uid in 
(select uid 
from user_info
where level > 5)
group by er.exam_id
order by uv desc,avg_score asc

正确代码：

select er.exam_id,
any_value(count(distinct er.uid)) uv,
round(avg(er.score),1) avg_score
from examination_info ei join exam_record er
on ei.exam_id = er.exam_id
where ei.tag = "SQL" 
and date_format(submit_time,'%Y%m%d')=date_format(release_time,'%Y%m%d')
and er.uid in 
(select uid 
from user_info
where level > 5)
group by er.exam_id
order by uv desc,avg_score asc

复盘：

（1）同一天，不能用day函数，0901和0201的day都是1，但是不是同一天。

 

（2）计算人数时，要加distinct才对：

原数据有这种离谱的情况？？

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

我的正确代码：直接三表连接

select level,count(level) level_cnt
from user_info u 
join exam_record er
on u.uid = er.uid
join examination_info ei
on ei.exam_id = er.exam_id
where ei.tag = 'SQL'
and er.score>80
group by level

嵌套子查询的方法代码：

SELECT level, 
COUNT(level) AS level_cnt
FROM user_info
WHERE uid IN (
              SELECT DISTINCT uid
              FROM exam_record
              WHERE score > 80
              AND exam_id IN (
                                SELECT exam_id 
                                FROM examination_info 
                                WHERE tag = 'SQL'
                              )
             )
GROUP BY level
ORDER BY level_cnt DESC;

2合并查询

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

我的代码：分别查询然后用union all合并起来，但是答案错了

select exam_id tid,
count(distinct er.uid) uv,
count(distinct pr.submit_time) pv
from exam_record er join practice_record pr
using(uid)
group by exam_id

union all

select question_id tid,
count(distinct er.uid) uv,
count(distinct pr.submit_time) pv
from exam_record er join practice_record pr
using(uid)
group by question_id

正确答案：

select * from 
(SELECT exam_id tid,count(DISTINCT uid) uv,count(uid) pv from exam_record
group by exam_id
order by uv desc,pv desc)a
UNION ALL
SELECT * FROM
(SELECT question_id tid,count(DISTINCT uid) uv,count(uid) pv from practice_record
GROUP BY question_id
order by uv desc,pv desc)b

我的代码改正：这个题最后不要合并，题目和试卷在不同的表里，分别查询在合并就好了

select exam_id tid,
count(distinct er.uid) uv,
count(er.uid) pv
from exam_record er
group by exam_id

union all

select question_id tid,
count(distinct pr.uid) uv,
count(pr.uid) pv
from practice_record pr
group by question_id

还没排序：

但是使用 union 和 多个order by 不加括号 【报错】，order by 在 union 连接的子句不起作用，但是在子句的子句中起作用。

方法一：所以加两个order的话正确要这样写：

#正确代码
select * from 
(
select 
    exam_id as tid,
    count(distinct uid) as uv,
    count(uid) as pv
from exam_record a
group by exam_id
order by uv desc, pv desc
) a
union
select * from 
(
select 
    question_id as tid,
    count(distinct uid) as uv,
    count(uid) as pv
from practice_record b
group by question_id
order by uv desc, pv desc
) attr

方法二：或者利用left(str,length) 函数： str左边开始的长度为 length 的子字符串，在本例中为‘9’和‘8’。

order by left(tid,1) desc,uv desc,pv desc

解释：试卷编号以‘9’开头、题目编号以‘8’开头，对编号进行降序就是对"试卷"和"题目"分别进行排序。

(
    #每份试卷被作答的人数和次数
    select
        exam_id as tid,
        count(distinct uid) as uv,
        count(*) as pv
from exam_record
group by exam_id
)
union
(
    #每个题目被作答的人数和次数
    select
        question_id as tid,
        count(distinct uid) as uv,
        count(*) as pv
from practice_record
group by question_id
)
#分别按照"试卷"和"题目"的uv & pv降序显示
order by left(tid,1) desc,uv desc,pv desc

2.2分别满足两个活动的人

我的垃圾代码：不知道新的值怎么弄

(
    select uid
    from exam_record
    group by 1001
    having score>85
)t


select uid	t.activity
from examination_info ei join exam_record er
on ei.exam_id = er.exam_id

大佬代码：

(select uid,'activity1' as activity
from exam_record er
where year(start_time)='2021'
group by uid
having min(score)>=85)
union ALL
(select uid,'activity2' as activity
from exam_record er left join examination_info ei on er.exam_id=ei.exam_id
where year(start_time)='2021' and ei.difficulty='hard' and score>=80 
and timestampdiff(second,er.start_time,er.submit_time)<= ei.duration*30
group by uid)
order by uid;

复盘：

（1）select uid,'activity1' as activity...，这样就把activity这一列就设置出来了。

（2）时间差函数：timestampdiff，如计算差多少分钟，timestampdiff(minute,时间1,时间2)，是时间2-时间1，单位是minute。

这里是至少有一次用了一半时间就完成：

完成时间<=考试时长/2 （单位为分钟minute）

完成时间<=考试时长*60/2 =考试时长*30（单位为秒second）

timestampdiff(second,er.start_time,er.submit_time)<= ei.duration*30

（3）每次试卷得分都能到85分，相当于最低分min>=85

3连接查询

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

我的报错代码：看来不是这么简单粗暴的事情 

select u.uid,
count(er.submit_time) exam_cnt,
count(pr.submit_time) question_cnt
from user_info u join exam_record er
on u.uid = er.uid
join practice_record pr
on pr.uid = u.uid
join examination_info ei
on ei.exam_id = er.exam_id
where year(er.submit_time)='2021'
group by u.uid
having ei.tag = 'SQL'
and ei.difficulty = 'hard'
and u.level = 7
and avg(er.score)>80

正确代码：

# select er.uid as uid,
# count(distinct er.submit_time) as exam_cnt,
# count(distinct pr.submit_time) as question_cnt
select er.uid as uid,
count(distinct er.exam_id) as exam_cnt,
count(distinct pr.id) as question_cnt

from exam_record er 
left join practice_record pr 
on er.uid=pr.uid 
and year(er.submit_time)=2021 
and year(pr.submit_time)=2021

where er.uid in(
        select er.uid
        from exam_record er 
        left join examination_info ei 
        on er.exam_id = ei.exam_id
        left join user_info ui 
        on er.uid = ui.uid 
        where tag='SQL' 
        and difficulty='hard' 
        and level = 7
        group by er.uid
        having avg(score) > 80
        ) 
group by er.uid
order by exam_cnt,question_cnt desc

复盘：

有4个表，很多个条件

（1）先通过子查询中连接，er,ui和ei筛选出高难度SQL试卷得分平均值大于80并且是7级的红名大佬（返回用户uid）

（2） 再统计这些大佬的2021年试卷总完成次数，和题目总练习次数

（3）注意第（2）步中连接是左连接，不应该出现试卷为null，题目不为null的情况！

from exam_record er left join practice_record pr

（4）不懂为什么不能用 er.submit_time， pr.submit_time来计算

# select er.uid as uid,
# count(distinct er.submit_time) as exam_cnt,
# count(distinct pr.submit_time) as question_cnt
select er.uid as uid,
count(distinct er.exam_id) as exam_cnt,
count(distinct pr.id) as question_cnt

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

我的代码

正确代码

复盘

下课了 明天再说

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/679702.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

【从零开始部署SAM（Segment Anything Model ）大模型 3 Ubuntu20 离线部署 C++】

【从零开始部署SAM（Segment Anything Model ）大模型 3 Ubuntu20 离线部署 C++】

这里是目录 总览环境配置模型准备Moble SAM onnx模型获取Moble SAM pre onnx模型获取 运行cmakelist 运行结果 总览 相比于使用python离线部署SAM大模型&#xff0c;C要麻烦的多&#xff0c;本篇的部署过程主要基于项目&#xff1a;https://github.com/dinglufe/segment-anyth…
阅读更多...
Python中上下文管理器解析

Python中上下文管理器解析

文章目录 基本原理上下文管理器的工作原理自定义上下文管理器enter和exitcontextlib 模块 异常处理 Python中的上下文管理器&#xff08;Context Manager&#xff09;是一种用于管理资源的机制&#xff0c;特别是在文件操作、数据库连接和锁定等场景中非常有用。上下文管理器通…
阅读更多...
python替换“${}“占位符为变量，实现读取配置文件

python替换“${}“占位符为变量，实现读取配置文件

文章目录 背景1、定义正则表达式2、替换变量占位符3、实现功能 背景 使用python编写小工具&#xff0c;有一个配置文件&#xff0c;希望实现类似shell命令的&#xff0c;定义变量并且使用${}或者$来引用。如果有好的建议欢迎讨论。 配置文件示例内容如下: D:\project\test\pr…
阅读更多...
Windows下通过Ollama部署使用本地模型

Windows下通过Ollama部署使用本地模型

Windows下通过Ollama部署使用本地模型 下载Ollama 安装主程序 Ollama下载exe&#xff0c;直接下一步下一步没有设置可以更改 windows默认安装路径在C盘 安装后会自动将该路径加入环境变量 双击图标运行后状态栏会出现小图标&#xff0c;右键有退出、打开日志文件夹按钮 通过…
阅读更多...
mac配置Personal Access Tokens

mac配置Personal Access Tokens

背景 在macbook环境中&#xff0c;使用idea、android studio、xcode时&#xff0c;使用gitlab需要登录&#xff0c;而直接使用文明密码是不允许登录的&#xff0c;这时就需要换种方式&#xff0c;这里有两种&#xff1a;ssh、Access Tokens&#xff0c;在公用电脑上推荐使用Ac…
阅读更多...
Linux网络的DHCP配置

Linux网络的DHCP配置

文章目录 DHCP配置DHCP流程简述DHCP优点DHCP的分配方式DHCP的租约过程DHCP配置实验实验1实验2 DHCP配置 DHCP&#xff1a;动态主机配置协议 服务端和客户端 服务端&#xff1a;server&#xff0c;提供某种特定的服务 客户端&#xff1a;client&#xff0c;使用服务端提供的服…
阅读更多...
R语言探索与分析18-基于时间序列的汇率预测

R语言探索与分析18-基于时间序列的汇率预测

一、研究背景与意义 汇率是指两个国家之间的货币兑换比率&#xff0c;而且在国家与国家的经济交流有着举足轻重的作用。随着经济全球化的不断深入&#xff0c;在整个全球经济体中&#xff0c;汇率还是一个评估国家与国家之间的经济状况和发展水平的一个风向标。汇率的变动会对…
阅读更多...
Thread Local六连问，你扛得住吗？

Thread Local六连问，你扛得住吗？

一、Thread Local 是什么? 线程本地变量。当使用ThreadLocal维护变量时&#xff0c;ThreadLocal为每个使用该变量的线程提供独立的变量副本&#xff0c;所以每个线程都可以独立地改变自己的副本&#xff0c;而不影响其他线程&#xff0c;做到了线程隔离。 二、Thread Local …
阅读更多...
HTB 靶场 Mailing 未完待续

HTB 靶场 Mailing 未完待续

访问网页 在/etc/hosts 添加ip和域名 hosts 文件包含ip地址与主机名之间的映射&#xff0c;还包括主机的别名。 Linux系统所有程序查询/etc/hosts文件解析对主机名或者域名的IP地址。没有找到就需要使用DNS服务器解释域名。 DNS原理 1 输入域名&#xff0c;在本地缓存服务…
阅读更多...
【工具】Vmware17 安装mac（13.6.7）虚拟机

【工具】Vmware17 安装mac（13.6.7）虚拟机

目录 0.简介 1.环境 2.详细步骤 2.1下载mac镜像&#xff08;可以选择你所需要的&#xff09; 2.2 VMware安装 1&#xff09;创建新的虚拟机 2&#xff09;选择【典型】&#xff0c;点击下一步 3&#xff09;选择【安装程序光盘映像文件】&#xff0c;点击浏览&#xff…
阅读更多...
公派/自费访问学者申请出国访学的常见问题解答（下）

公派/自费访问学者申请出国访学的常见问题解答（下）

06、学术背景和研究成果要求&#xff1f; 访学是面向学术单位和企事业单位开放的。 针对学术单位&#xff0c;比如高校与科研院所&#xff0c;学科内涉及的论文发表&#xff0c;课题研究&#xff0c;专利&#xff0c;著作&#xff0c;含金量较高的奖项等背景都是国外比较看重…
阅读更多...
rollup.js（入门篇）

rollup.js（入门篇）

前沿 Rollup 是一个用于 JavaScript 的模块打包工具&#xff0c;它将小的代码片段编译成更大、更复杂的代码&#xff0c;例如库或应用程序。它使用 JavaScript 的 ES6 版本中包含的新标准化代码模块格式&#xff0c;而不是以前的 CommonJS 和 AMD 等特殊解决方案。ES 模块允许…
阅读更多...
亚马逊云，不想失去云计算的“铁王座”

亚马逊云，不想失去云计算的“铁王座”

文&#xff5c;白 鸽 编&#xff5c;王一粟 “生成式AI时代的黎明已经来临。” 亚马逊全球副总裁、亚马逊云科技大中华区总裁储瑞松在2024年亚马逊云中国科技峰会上&#xff0c;再次强调了生成式AI对于亚马逊云科技和整个行业的重要性。 事实上&#xff0c;从去年开始&a…
阅读更多...
JVM 运行流程

JVM 运行流程

JVM 是 Java 运行的基础&#xff0c;也是实现一次编译到处执行的关键&#xff0c;那么 JVM 是如何执行的呢&#xff1f; JVM 执行流程 程序在执行之前先要把java代码转换成字节码&#xff08;class 文件&#xff09;&#xff0c; JVM 首先需要把字节码通过一定的 方式 类加…
阅读更多...
独立游戏之路 -- 上架TapTap步骤和注意事项

独立游戏之路 -- 上架TapTap步骤和注意事项

个人开发者游戏上架TapTap上架步骤和注意事项 一、TapTap 介绍二、独立游戏上架 TapTap 的步骤2.1 创建游戏2.2 提交游戏审核2.3 TapTap 平台上发布。 三、注意事项3.1 关于备案3.2 遵守 TapTap 的规定3.3 保证游戏质量 四、常见问题4.1 隐私政策问题4.2 先发布还是先优化&…
阅读更多...
Mybatis02-CRUD操作及配置解析

Mybatis02-CRUD操作及配置解析

1、CRUD 1.namespace namespace中的包名要和Dao/Mapper 接口的包名一致&#xff01; 1个Dao接口类对应1个mapper&#xff0c;也对应1个namespace&#xff0c; 1个Dao接口中的方法对应1个namespace中一个SQL语句 2.CRUD id&#xff1a;对应的namespace接口中的方法名resul…
阅读更多...
【读书笔记】曼陀罗思考法

【读书笔记】曼陀罗思考法

目录 1 起源2 路径示例——人生规划设计 3 分类3.1 扩展型“扩展型”曼陀罗——使用方法 3.2 围绕型 4 注意事项 1 起源 曼陀罗在梵文中意味着“圣地”&#xff0c;象征着宇宙的秩序和内心的神圣结构。 “曼陀罗思考法”&#xff0c;是由日本学者今泉浩晃发明的方法&#xff…
阅读更多...
从零开始实现自己的串口调试助手(6) -换行问题

从零开始实现自己的串口调试助手(6) -换行问题

解决接收的自动换行 自动换行原因 --> 我们以append发送 会自动换行 换个api 即可 --> 我们换成 insertPlainText 添加自动换行 实现添加新行 修改的函数代码: on_btnSendContext_clicked void Widget::on_btnSendContext_clicked() {// const char * sendData ui->…
阅读更多...
C#WPF数字大屏项目实战10--不良指标分页

C#WPF数字大屏项目实战10--不良指标分页

1、区域划分 2、区域布局 3、视图模型 4、控件绑定 5、运行效果 走过路过&#xff0c;不要错过&#xff0c;欢迎点赞&#xff0c;收藏&#xff0c;转载&#xff0c;复制&#xff0c;抄袭&#xff0c;留言&#xff0c;动动你的金手指&#xff0c;财务自由
阅读更多...
java常见api :Math System

java常见api :Math System

一. Math类 1.定义在那个包 java.lang包下 2.作用 (1)是一个帮助我们用于进行数学计算的工具类 (2)私有化构造方法,所有的方法都是静态的 3.常用的方法 &#xff08;1&#xff09;获取绝对值 System.out.println(Math.abs(-88)); 取值范围&#xff1a; -2147483648到21…
阅读更多...
最新文章

Copyright @ 2022~2023