牛客_刷题本_SQL篇快速入门

2024 年 8 月 7 日 14点热度 0人点赞 0条评论

文章目录

1.查询结果去重

题目： 现在运营需要查看用户来自于哪些学校，请从用户信息表中取出学校的去重数据。
在这里插入图片描述

解题思路： 二种方式：
①.distinct 关键字去重，放在列的前面使用。
②.分组SELECT university from user_profile group by university以分组来筛选出去重的结果。
代码：

select distinct university from user_profile

select university from user_profile group by university

2.查询结果限制返回行数

题目： 现在运营只需要查看前2个用户明细设备ID数据，请你从用户信息表 user_profile 中取出相应结果。
在这里插入图片描述

解题思路：
使用limit限制返回行数，limit使用方法：
①.选取前n行

select 数据列 from 数据表 limit 0,5
#或者
select 数据列 from 数据表 limit 5

②.选取中间任意几行（limit offset）（offset起始位置）

#查询第n到m行
select 数据列from 数据表 limit m-n offset n
#选取中间任意几行（offset...fetch）
select 数据列 from 数据表 n rows fetch next m-n rows only

代码：

select device_id from user_profile limit 2

3.将查询后的列重新命名

题目： 现在你需要查看前2个用户明细设备ID数据，并将列名改为 ‘user_infos_example’,，请你从用户信息表取出相应结果。
在这里插入图片描述

解题思路：
这里主要用到了起别名关键字as 以及组合限制查询limit索引，个数。其中as可以省略，索引0可以省略。
代码：

select device_id as user_info_example from user_profile limit 2

4.分组计算练习题

题目： 现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。
在这里插入图片描述

解题思路：
题目中提到每个学校不同性别的用户，说明需要根据学校和性别进行分组：group by gender,university。计算用户数用count(device-id)，30天内活跃天数用avg(active_days_within_30)，平均发帖数量用avg(question_cnt)
注意：
分组语句的一般形式：
[GROUP BY ]
[HAVING ]
①GROUP BY子句中的分组依据列必须是表中存在的列名，不能使用AS子句指派的结果集列的别名。
②带有GROUP BY 子句的SELECT语句的查询列表中只能出现分组依据列或统计函数，因为分组后每个组只返回一行结果。
代码：

select gender,university, 
count(device_id),avg(active_days_within_30),avg(question_cnt)
from user_profile 
group by gender,university

5.分组过滤练习题

题目： 现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。
在这里插入图片描述

解题思路：
①限定条件：平均发贴数低于5或平均回帖数小于20的学校，avg(question_cnt)<5 or avg(answer_cnt)<20，聚合函数结果作为筛选条件时，不能用where，而是用having语法，根据输出格式配合重命名，表头重命名：as 即可；
②按学校输出：需要对每个学校统计其平均发贴数和平均回帖数，因此group by university
代码：

select university,
avg(question_cnt) as avg_question_cnt,
avg(answer_cnt) as avg_answer_cnt
from user_profile
group by university
having avg_question_cnt<5 or avg_answer_cnt<20

6.统计每个学校的答过题的用户的平均答题数

描述：
运营想要了解每个学校答过题的用户平均答题数量情况，请你取出数据。在这里插入图片描述

解题思路：
按学校分组，group by university；在每个学校的分组内，用总答题数量除以总人数即可得到平均答题数量count(question_id) / count(distinct device_id)。
表连接：学校和答题信息在不同的表，需要做连接
表头重命名用as
代码：

select university,
count(question_id)/count(distinct qpd.device_id) as avg_answer_cnt
from question_practice_detail as qpd
inner join user_profile as up
on qpd.device_id=up.device_id
group by university

7.统计每个学校各难度的用户平均刷题数

题目： 运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据
在这里插入图片描述

解题思路：
每个学校：按学校分组group by university
不同难度：按难度分组group by difficult_level
平均答题数：总答题数除以总人数count(qpd.question_id) / count(distinct qpd.device_id)
来自上面信息三个表，需要联表，up与qpd用device_id连接，qd与qpd用question_id连接。
表头重命名：as
平均值精度：保留4位小数round(x, 4)
代码：

select
    university,
    difficult_level,
    round(count(qpd.question_id) / count(distinct qpd.device_id), 4) as avg_answer_cnt
from question_practice_detail as qpd
left join user_profile as up
on up.device_id=qpd.device_id
left join question_detail as qd
on qd.question_id=qpd.question_id
group by university, difficult_level

8.统计每个用户的平均刷题数

题目： 运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数，请取出相应数据
在这里插入图片描述

解题思路1：
限定条件：山东大学的用户 up.university=“山东大学”；
不同难度：按难度分组group by difficult_level
平均答题数：总答题数除以总人数count(qpd.question_id) / count(distinct qpd.device_id) 来自上面信息三个表，需要联表，up与qpd用device_id连接并限定大学，qd与qpd用question_id连接。
表头重命名：as
代码1：

select
    "山东大学" as university,
    difficult_level,
    count(qpd.question_id) / count(distinct qpd.device_id) as avg_answer_cnt
from question_practice_detail as qpd
 
inner join user_profile as up
on up.device_id=qpd.device_id and up.university="山东大学"
 
inner join question_detail as qd
on qd.question_id=qpd.question_id
 
group by difficult_level

解题思路2:
可以用左链接，以第二个表为基础，分别链接第一和第三个表
链接三个表后，以学校和难度分组
分组完成后用having 过滤
代码2：

SELECT t1.university,
t3.difficult_level,
COUNT(t2.result) / COUNT(DISTINCT t2.device_id) AS avg_answer_cnt
FROM question_practice_detail AS t2
LEFT JOIN user_profile AS t1
ON t2.device_id = t1.device_id
LEFT JOIN question_detail AS t3
ON t2.question_id = t3.question_id
GROUP BY t1.university, t3.difficult_level
HAVING t1.university = '山东大学';

9.去重 distinct；不去重union all

分别查看&结果不去重：所以直接使用两个条件的or是不行的，直接用union也不行，要用union all，分别去查满足条件1的和满足条件2的，然后合在一起不去重
在这里插入图片描述

10.多分支函数 case

CASE函数
是一种多分支的函数，可以根据条件列表的值返回多个可能的结果表达式中的一个。
可用在任何允许使用表达式的地方，但不能单独作为一个语句执行。
分为：
简单CASE函数
搜索CASE函数
简单 CASE函数：

CASE 测试表达式
WHEN 简单表达式1 THEN 结果表达式1
WHEN 简单表达式2 THEN 结果表达式2 …
WHEN 简单表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END

计算测试表达式，按从上到下的书写顺序将测试表达式的值与每个WHEN子句的简单表达式进行比较。
如果某个简单表达式的值与测试表达式的值相等，则返回第一个与之匹配的WHEN子句所对应的结果表达式的值。
如果所有简单表达式的值与测试表达式的值都不相等，
若指定了ELSE子句,则返回ELSE子句中指定的结果表达式的值；
若没有指定ELSE子句，则返回NULL。
在这里插入图片描述
搜索CASE函数

CASE
WHEN 布尔表达式1 THEN 结果表达式1
WHEN 布尔表达式2 THEN 结果表达式2 …
WHEN 布尔表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END

按从上到下的书写顺序计算每个WHEN子句的布尔表达式。
返回第一个取值为TRUE的布尔表达式所对应的结果表达式的值。
如果没有取值为TRUE的布尔表达式，
则当指定了ELSE子句时,返回ELSE子句中指定的结果；
如果没有指定ELSE子句，则返回NULL。
在这里插入图片描述

11.对查询结果排序

order by 列名 :默认是从大到小排序；
反序可在后面加DESC关键字

本作品采用知识共享署名 4.0 国际许可协议进行许可